Pages

Friday, October 4, 2013

TSQL Script to create Default Database Email

Recently we did a installation of some bulk SQL Server for various purpose. Now we needed database Email to be configured on each server and there had to be some standard. I worked out this my Making a generic script which can be used and run on any machine and which maintain symmetry
USE master
Go

sp_configure 'show advanced options', 1
GO

RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO

USE msdb
GO

DECLARE
    @profile    VARCHAR(100),
    @display    VARCHAR(100),
    @emailaddr    VARCHAR(100),
    @accountname    Varchar(100)

SELECT
    @profile    = 'MyProfileName_DB_Mail_Profile_' + @@ServerName,
    @display    = 'MyProfileName_DB_Mail_Account_' + @@ServerName,
    @emailaddr    = 'MyRequiredEmailAddress' + @@servername + '@myDomain.com',
    @accountname    = 'MyAlertName_Alert_Account';

-- Create Database Mail account
EXECUTE dbo.sysmail_add_account_sp 
        @account_name = @accountname,
        @description = 'MyDatabaseAccountName Alert Database Account', 
        @email_address = @emailaddr,
        @display_name = @display,
        @mailserver_name = 'MyMailserver.com';        

-- Create a Database Mail profile
EXECUTE dbo.sysmail_add_profile_sp
        @profile_name = @profile,
        @description = 'MyDatabaseEmailProfileName Alert Database Profile';

-- Add multiple Database Mail accounts to Mail profile
EXECUTE dbo.sysmail_add_profileaccount_sp
        @profile_name = @profile,
        @account_name = @accountname,
        @sequence_number = 1;

-- Grant the mail profile access to msdb public role & make it a default
EXECUTE dbo.sysmail_add_principalprofile_sp  
        @profile_name = @profile,
        @principal_name = 'public',
        @is_default = 1;

EXEC master.dbo.xp_instance_regwrite 
        N'HKEY_LOCAL_MACHINE', 
        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
        N'DatabaseMailProfile', 
        REG_SZ, 
        @profile;

GO

No comments:

Post a Comment