SQL Server provides the ability to send emails via its Database Mail solution.

The actual act of sending mail is done with the sp_send_dbmail stored procedure. But before you start sending emails from SQL Server, you need to enable and configure Database Mail.

You can do this with the SSMS GUI, or with T-SQL.

This article demonstrates how to do it with T-SQL. It assumes that Database Mail has never been enabled on your system, and therefore it goes through the steps involved in enabling it.

If you prefer to use the SSMS GUI, see How to Setup Database Mail in SQL Server (SSMS).

Step 1: Enable Database Mail XPs

The first thing you need to do before you send any mail from SQL Server is enable the Database Mail extended stored procedures (Database Mail XPs). These are in the msdb system database.

This also requires that “show advanced options” are enabled.

These are disabled by default. So if you’ve never used Database Mail on your system before, then you’ll need to enable them.

Here’s how to enable advanced options and the Database Mail XPs:

EXEC sp_configure 'show advanced options', '1';
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE
GO

There’s no need to restart SQL Server. The Database Mail XPs should now be available to use.

Microsoft actually recommends that advanced options such as this should be changed only by an experienced database administrator or certified SQL Server technician, but for the purpose of this article, I’ll assume you’re an experienced DB administrator who’s refreshing your memory 😉

In any case, in order to execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

Step 2: Create a Mail Account & Profile

Database Mail is sent via a profile, rather than a user account directly.

To send email with Database Mail you need to create a Database Mail account, a Database Mail profile, add the account to the profile, and then grant a user access to that profile. The user needs to be on the msdb database.

The T-SQL code to do this could look something like this:

-- Switch to the msdb database
USE msdb;

-- Create a user on the msdb database
CREATE USER Marge FOR LOGIN Marge;

-- Create a Database Mail account  
EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'DB Admin',  
    @description = 'Mail account for admin emails.',  
    @email_address = '[email protected]',  
    @replyto_address = '[email protected]',  
    @display_name = 'DB Automated Mailer',  
    @mailserver_name = 'smtp.example.com',
    @port = 25;  
  
-- Create a Database Mail profile  
EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'DB Admin Profile',  
    @description = 'Profile for admin emails.';  
  
-- Add the account to the profile  
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'DB Admin Profile',  
    @account_name = 'DB Admin',  
    @sequence_number = 1;
  
-- Grant the msdb user access to the Database Mail profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'DB Admin Profile',
    @principal_name = 'Marge',
    @is_default = 1;

You’ll need to replace the various details with your own. This also assumes that you specify a mail server that works, and that you use the correct port.

Note that I already had a login called Marge on my server. Here, I created a user on the msdb database for that login. Then in the last part, I granted that user access to the profile that I just created.

In this example, I created one Database Mail account and added it to the profile. You can add multiple accounts to a profile if you like. This can be useful as a failover when sending mail. If the first account fails, it will try the next one, and the next one, and so on.

Once you’ve the above code (with your own details), you should be able to send mail.

Step 3: Send Mail

As mentioned, the actual sending of mail is done with the sp_send_dbmail stored procedure.

Here’s an example:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'DB Admin Profile',  
    @recipients = '[email protected]',  
    @body = 'Your favorite SQL Server Agent job just failed',  
    @subject = 'SQL Server Agent Job: FAILED';

This is a simple email with the bare basics such as the recipient, subject line, email body, etc.

If you don’t specify a profile, the procedure will use the default private profile for the current user. If there’s no default private profile for the user, it will use the default public profile for the msdb database. If there’s no default public profile, you’ll get an error.

More Options

The sp_send_dbmail procedure accepts quite a few more arguments than what I’ve used in this example.

Here’s the official syntax for the sp_send_dbmail procedure:

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]  
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]  
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]  
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]  
    [ , [ @from_address = ] 'from_address' ]  
    [ , [ @reply_to = ] 'reply_to' ]   
    [ , [ @subject = ] 'subject' ]   
    [ , [ @body = ] 'body' ]   
    [ , [ @body_format = ] 'body_format' ]  
    [ , [ @importance = ] 'importance' ]  
    [ , [ @sensitivity = ] 'sensitivity' ]  
    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]  
    [ , [ @query = ] 'query' ]  
    [ , [ @execute_query_database = ] 'execute_query_database' ]  
    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]  
    [ , [ @query_attachment_filename = ] query_attachment_filename ]  
    [ , [ @query_result_header = ] query_result_header ]  
    [ , [ @query_result_width = ] query_result_width ]  
    [ , [ @query_result_separator = ] 'query_result_separator' ]  
    [ , [ @exclude_query_output = ] exclude_query_output ]  
    [ , [ @append_query_error = ] append_query_error ]  
    [ , [ @query_no_truncate = ] query_no_truncate ]   
    [ , [ @query_result_no_padding = ] @query_result_no_padding ]   
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

See Microsoft’s documentation for a detailed explanation of each argument.

Database Mail Reference

See Database Mail Tutorials for a comprehensive list of Database Mail tutorials. This can be a great reference, because each tutorial deals with a specific part of Database Mail.