T-SQL To Create Database Mail

I was wondering if there was a way to script out Database Mail so that the configuration was identical across multiple SQL Server instances. Unlike most other objects in SQL, there’s no obvious way to do this (typically I expect to see the option through the SSMS UI when right clicking on an object).

And then I thought of SQL Templates: I’ve not really used SQL Templates before, but SQL Server supplies default template scripts that help the user create objects in a database. I wondered if there was a template for Database Mail, and it turns out there is! And it’s really good also! It’s robust with it’s checks and configures most things that I needed to run. I made some changes and have provided the full script. Full credit goes to the Template though; it’s a potential time saver and helps a DBA document Database Mail for further installs of SQL Server.

-------------------------------------------------------------
-- Database Mail Configuration
-- The script does not grant access to the new profile for
-- any database principals. Use msdb.dbo.sysmail_add_principalprofile
-- to grant access to the new profile for users who are not
-- members of sysadmin.
-------------------------------------------------------------

DECLARE @profile_name sysname,
@account_name sysname,
@SMTP_servername sysname,
@email_address NVARCHAR(128),
@display_name NVARCHAR(128),
@profile_description NVARCHAR(128),
@account_description NVARCHAR(128);

-- Profile name. Replace with the name for your profile
SET @profile_name = 'SQL Server Email Notification Service';
SET @profile_description = 'Email notification service for SQL Server'

-- Account information. Replace with the information for your account.

SET @account_name = 'SQL Server Notification Service';
SET @SMTP_servername = 'smtp.smtp.com';
SET @email_address = 'it@email.com';
SET @display_name = 'srvname';
SET @account_description = @display_name+' - SQL Server Email Notification Services'

-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
RAISERROR('The specified Database Mail profile (SQL Server Email Notification Service) already exists.', 16, 1);
GOTO done;
END;

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
RAISERROR('The specified Database Mail account (SQL Server Notification Service) already exists.', 16, 1) ;
GOTO done;
END;

-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;

DECLARE @rv INT;

-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
@account_name = @account_name,
@email_address = @email_address,
@display_name = @display_name,
@mailserver_name = @SMTP_servername;

IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail account (SQL Server Notification Service).', 16, 1) ;
GOTO done;
END

-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
@profile_name = @profile_name ;

IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail profile (SQL Server Email Notification Service).', 16, 1);
ROLLBACK TRANSACTION;
GOTO done;
END;

-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1 ;

IF @rv<>0
BEGIN
RAISERROR('Failed to associate the speficied profile with the specified account (SQL Server Notification Service).', 16, 1) ;
ROLLBACK TRANSACTION;
GOTO done;
END;

COMMIT TRANSACTION;

done:

GO

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s