SQL Server Database Mail setup via SQL

Setting up SQL Server Database Mail is relatively simple when using SQL Server Management Studio, thanks to the graphical setup wizard it provides for most SQL Server editions.

However, SQL Server Express, the free edition of SQL Server, does not offer the facility to configure Database Mail via a user interface.

Thankfully, once you understand the concepts of Database Mail, setting it up for SQL Server Express is quite straightforward using T-SQL stored procedures.

This article covers how to set up Database Mail via SQL scripts and includes examples of how to send emails from SQL by leveraging the Database Mail setup.

Concepts

In my previous article, SQL Server Database Mail setup simplified, I covered the basics of how Database Mail works, along with step-by-step instructions for how to set it up via the setup wizard in SQL Server Management Studio. I recommend that you read this article if you are currently unfamiliar with Database Mail as it will help you to visualise the concepts.

In brief, Database Mail allows you to configure one or more ‘profiles’. These profiles can in turn contain one or more mail accounts that are added to the profile in priority order.

When you send an email using Database Mail you must select a profile to use. The highest priority mail account that is linked to the profile is used to send the email on the first attempt. If more than one mail account has been added to the profile, the next mail account in the priority list will be used if there is a problem sending an email using the previous account. This provides redundancy in the case of mail server outages or general transient issues.

A Database Mail profile also contains settings such as the number of retry attempts to make whenever an email fails to send and other things like logging levels.

Database Mail is a highly useful mechanism for sending alerts directly from your database engine and it provides a secure way to store and access mail accounts thanks to its granular permissions system.

Now that the basic concepts are understood, let’s proceed to set up Database Mail via SQL in the sections that follow.

SQL setup

If you connect to a SQL Server Express instance you will notice that the ‘Database Mail’ option is not available under the ‘Management’ node. This means that SQL needs to be used for all of the Database Mail setup work.

When setting up Database Mail via SQL you need to know which stored procedures to call and have an understanding of the parameters that are required by these procedures.

As usual, the Microsoft Docs for Database Mail are a reliable source of information and I will link to the relevant documentation for each stored procedure that is used further below.

Enabling Database Mail

Since we don’t have the Database Mail setup wizard to help us out, we need to enable Database Mail via SQL before we can begin creating profiles and accounts using the specific Database Mail stored procedures.

Server configuration

To enable Database Mail, connect to the appropriate SQL Server instance and execute the following SQL.

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

If everything works ok you should see the following output.

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Database Mail XPs’ changed from 1 to 1. Run the RECONFIGURE statement to install.

In the above SQL, we, first of all, use the sp_configure stored procedure to turn on the ‘show advanced options’ setting by passing in a value of 1, otherwise, we will receive a message as follows.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option ‘Database Mail XPs’ does not exist, or it may be an advanced option.

After showing the advanced options, the sp_configure stored procedure is used a second time to turn on the Database Mail feature, again by passing in a value of 1.

What is sp_configure?

sp_configure is a built-in stored procedure that can be used to display or change global SQL Server configuration settings.

Note that it is important to call RECONFIGURE after updating a server setting using sp_configure. This will apply the currently configured value of a configuration option changed by sp_configure.

Turning off advanced options

Before moving on, I recommend that you turn off advanced options, as follows.

sp_configure 'show advanced options', 0;
GO
RECONFIGURE;

Passing in a value of 0 indicates that the specified option should be disabled.

Turning off advanced options helps to minimise the risk of other server-level settings being accidentally changed.

Creating a profile

Now that Database Mail is enabled, we can go ahead and create a profile to hold our mail accounts.

To do this, execute the following SQL.

EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Default';

The above SQL is the simplest possible statement that can be issued to create a new Database Mail profile. At a minimum, the sysmail_add_profile_sp stored procedure requires a Profile Name to be specified using the @profile_name parameter.

Note that a unique Profile Name must be specified. Duplicates are not allowed.

A Description for the profile may also be specified by adding the @description parameter.

Note that the usage of the msdb.dbo. database and schema prefix in the above example is important. If we don’t specify this, the sysmail_add_profile_sp stored procedure won’t work unless we are running our query in the context of the msdb system database.

Granting permissions

The next step is to grant the necessary permissions required to allow a database user or role to use the Database Mail profile that we have just created.

Database Mail allows for very granular permissions, with profiles being private by default. However, as per my previous article, we are going to set up a ‘Public’ profile to keep things as simple as possible.

To make things public, execute the SQL below.

EXEC msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name   = 'Default',
    @principal_name = 'public',
    @is_default     = 1;

In the above example, the sysmail_add_principalprofile_sp stored procedure is used to grant public access to the profile that we created.

Note that you should always carefully consider your Database Mail permissions when operating in a production environment. Check out this Microsoft Docs page for more information.

By setting the @is_default parameter to 1, we are configuring the new profile as the default profile. This means that we don’t need to specify a particular profile to use when sending an email via Database Mail.

Note that only one profile can be set as the default.

Now that permissions have been granted we can move on to setting up a mail account.

Creating a mail account

The primary purpose of a mail account is to hold the SMTP details that are required to authenticate with a mail server and deliver an email message.

Note that a mail account is independent of a profile and can be linked to more than one profile if required.

To create a new mail account update the parameter values in the SQL below and then execute.

EXEC msdb.dbo.sysmail_add_account_sp
    @account_name    = 'Gmail',
    @email_address   = 'example@gmail.com',
    @display_name    = 'SQL Notifications',
    @mailserver_name = 'smtp.gmail.com',
    @port            = 587,
    @enable_ssl      = 1,
    @username        = 'example@gmail.com',
    @password        = '*****************';

The sysmail_add_account_sp stored procedure is used to create a new mail account with the specified Account Name and SMTP details.

Note that some of the parameters such as @display_name are optional.

Add a mail account to a profile

The last setup step is to add the mail account we just created to the profile.

We can do this using the following SQL.

EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name    = 'Default',
    @account_name    = 'Gmail',
    @sequence_number = 1;

When the sysmail_add_profileaccount_sp stored procedure is executed, we must pass in the name of the profile and the name of the mail account, as well as the ‘sequence number’.

The @sequence_number parameter indicates the priority order that will be assigned to the mail account against the profile. Since more than one mail account can be added to a profile, the Database Mail engine will attempt to send emails using the mail account with the highest priority first, then the second, and so on.

Everything should now be ready for sending our first test email!

Setup undo

Before we proceed to the testing section, please be aware that if at any point you need to undo anything you can use the ‘delete’ variations of the Database Mail stored procedures as shown below.

EXEC msdb.dbo.sysmail_delete_principalprofile_sp
    @profile_name = 'Default';
 
EXEC msdb.dbo.sysmail_delete_profileaccount_sp
    @profile_name = 'Default';
 
EXEC msdb.dbo.sysmail_delete_account_sp
    @account_name = 'Gmail';
 
EXEC msdb.dbo.sysmail_delete_profile_sp
    @profile_name = 'Default';

You can also disable Database Mail as follows.

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

If you’ve been following along with all of the steps and you’ve executed all of the SQL included in this sub-section, your SQL Server setup should now be back to the way that it was prior to any of the Database Mail setup work.

Testing

Now that we have a profile and an associated mail account, we can run a test to verify that our Database Mail setup is valid.

Sending an email

To send an email the following SQL can be used, adjusting the parameters to suit the specific setup.

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Default',  
    @recipients   = 'example@live.com',  
    @subject      = 'Database Mail SQL Test',
    @body         = 'Hello from Database Mail!';

The sp_send_dbmail stored procedure provides a convenient way to send emails and offers a lot of customisation options via the numerous parameters that can be passed into it.

Make sure to specify your own email address as the value of the @recipients parameter when running your test.

After executing the SQL, you should see a message similar to the following.

Mail (Id: 1) queued.

Now check your inbox to see if the test email was received.

If you are having issues, I recommend that you check out the Test email problems section of my previous Database Mail article to get some ideas on what to try when troubleshooting.

You can also check out the official General Database Mail troubleshooting steps page.

Before wrapping up, I encourage you to check out the Microsoft Docs for sp_send_dbmail where you can explore all of the possible parameters that can be specified when executing the stored procedure.

Summary

In summary, I have provided a brief overview of SQL Server Database Mail concepts.

I have walked through enabling Database Mail, creating a profile, granting permissions, creating a mail account, and adding a mail account to a profile, all using T-SQL stored procedures.

Lastly, I have provided an example of how to send an email via Database Mail from SQL.