By Richie Lee

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 = '[email protected]';
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