How to Set Up SQL Server Database Mail in One Easy Script

Database Mail in Microsoft SQL Server is a powerful, but under-utilized ally for any Database Administrator. It's been around for years but often goes un-noticed to this day. I find myself relying upon Database Mail (let's just call it DBM through the remainder of the article to make it easy on me) to field such tasks as:

If any of those things sound like functionality you'd like to implement then this is the article you'll want to read.

Where is Database Mail in SQL Server Management Studio

DBM is located in the SQL Server Agent node of the Object Explorer window:

Double-clicking Database Mail will launch a process to step you through creating a DBM profile and an account to associate it with.  It's important before I go any farther that I clear up some terminology though between accounts and profiles.

Database Mail Accounts are closely associated to an SMTP (Simple Mail Transfer Protocol) account. The DBM account is essentially the information stored about the mail server, the security process to connect to the mail server, and how email sender in this process will be identified to recipients. You need to supply the following criteria when setting up a DBM account:

Database Mail Profiles are no more than identifiers for SQL Server to use to associate a request (the what) to the logistical information for the delivery (the how). All that is required is a name to identify the profile, an optional description for the profile, and then the account name to link to the profile. Multiple accounts can be associated with a given profile and if the first account fails to send a transmission the next account will be used.

Now that you know the information you'll need to create DBM Profiles and Accounts I want to move on to the script you'll run to do this all in one query.

Templates

This query uses Template Parameters. If you want to understand what these are please check out the related articles before proceeding to the code

You don't necessarily need to know all the information in those articles however. You can simply copy the query below into a new query window in SQL Server Management Studio active on the instance you want to configure for DBM and use the keyboard shortcut of Control + Shift + M or navigate to the menu bar and select Query then Specify Values for Template Parameters from the drop-down menu.

The Query

Now for what you've been reading on until the end for: the do-it-all script.

--================================================================-- DATABASE MAIL CONFIGURATION--================================================================--==========================================================-- Create a Database Mail account--==========================================================EXECUTE msdb.dbo.sysmail_add_account_sp    @account_name = '',    @description = '',    @email_address = '',    @replyto_address = '',    @display_name = '',    @mailserver_name = '',@port = ;--==========================================================-- Create a Database Mail Profile--==========================================================DECLARE @profile_id INT, @profile_description sysname;SELECT @profile_id = COALESCE(MAX(profile_id),1) FROM msdb.dbo.sysmail_profileSELECT @profile_description = 'Database Mail Profile for ' + @@servername EXECUTE msdb.dbo.sysmail_add_profile_sp    @profile_name = '',    @description = @profile_description;-- Add the account to the profileEXECUTE msdb.dbo.sysmail_add_profileaccount_sp    @profile_name = '',    @account_name = '',    @sequence_number = @profile_id;-- Grant access to the profile to the DBMailUsers roleEXECUTE msdb.dbo.sysmail_add_principalprofile_sp    @profile_name = '',    @principal_id = 0,    @is_default = 1 ;--==========================================================-- Enable Database Mail--==========================================================USE master;GOsp_CONFIGURE 'show advanced', 1GORECONFIGUREGOsp_CONFIGURE 'Database Mail XPs', 1GORECONFIGUREGO --EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N''--EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1--GOEXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 0GO--==========================================================-- Review Outcomes--==========================================================SELECT * FROM msdb.dbo.sysmail_profile;SELECT * FROM msdb.dbo.sysmail_account;GO--==========================================================-- Test Database Mail--==========================================================DECLARE @sub VARCHAR(100)DECLARE @body_text NVARCHAR(MAX)SELECT @sub = 'Test from New SQL install on ' + @@servernameSELECT @body_text = N'This is a test of Database Mail.' + CHAR(13) + CHAR(13) + 'SQL Server Version Info: ' + CAST(@@version AS VARCHAR(500))EXEC msdb.dbo.[sp_send_dbmail]     @profile_name = ''  , @recipients = ''  , @subject = @sub  , @body = @body_text--================================================================-- SQL Agent Properties Configuration--================================================================EXEC msdb.dbo.sp_set_sqlagent_properties @databasemail_profile = '', @use_databasemail=1GO

The script is broken down into the following sections:

The GUI process in SQL Server Management Studio does not cover enabling Database Mail on the instance nor assigning a profile to SQL Server Agent. The script saves you the work of having to set these in the server's Facets and in the SQL Server Agent configuration respectively.

When selecting to replace the template parameters you'll see a screen similar to this:

While some of the parameters have default values assigned there is no need to accept these values. They're mainly in place to illustrate what default values look like in template parameters within the script. As an example, this is what that form would look like if I populated it with values that would be appropriate for my test instance:

Once you hit OK, these parameters will replace the construct in the script and you'll have a working install of Database Mail on your instance. It has taken you longer to read this article than it will to set up Database Mail!