In SQL Server 2005 and higher, there is database wizard to configure the database mail, but no easy way to ask for a script of the current configuration.
Here is a little SQL script which will:
- activate Database Mail
- Create a default SMTP profile called sql_alert_profile and attach an SMTP account called account_smtp_public to it
- Send Test E-Mail
- Create one or more operators
You may wonder why there is a test mail embedded in this script whereas the contextual menu of the Database mail already provides one. It is just a way to follow the agile principles by providing automated tests.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 |
-- DATABASE MAIL SETUP -- -- DJ - 20141117 - 1.1 - handles named instances DECLARE @servername varchar(255) DECLARE @smtpserver varchar(255) DECLARE @smtpport int DECLARE @testmailrecipients varchar(255) DECLARE @fromaddress nvarchar(128) -- 0. Some variables to be changed if needed SET @servername = CAST(SERVERPROPERTY('ServerName') AS varchar(255)) SET @smtpserver = 'mail.domain.com' SET @smtpport = 25 SET @testmailrecipients = 'youraddress@domain.com' SET @fromaddress = 'sqlserver.'+REPLACE(@servername,'\','.')+'@domain.com' -- SMTP RFC doesn't allow backslashes in names -- Here we go PRINT '--- About to set up database mail --' PRINT 'Using SMTP Server ' + @smtpserver + ':' + CAST(@smtpport AS VARCHAR(5)) -- 1. Enable the db mail feature at server level -- Enabling Database Mail exec sp_configure 'show advanced options',1 reconfigure exec sp_configure 'Database Mail XPs',1 reconfigure -- 2.Enable service broker in the MSDB database -- normally you don't need it -- USE [master] -- GO -- ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT --3. Creating a Profile PRINT '-- Setting profile ' DECLARE @serverdescription nvarchar(max) SET @serverdescription = 'Mail Service for instance ' + @servername IF EXISTS(SELECT 1 FROM msdb.dbo.sysmail_profile WHERE name = 'sql_alert_profile' ) exec msdb.dbo.sysmail_delete_profile_sp @profile_name= 'sql_alert_profile' EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'sql_alert_profile', @description = @serverdescription -- 4. Create a Mail account. PRINT '-- Setting Account' IF EXISTS(SELECT 1 FROM msdb.dbo.sysmail_account WHERE name = 'account_smtp_public' ) exec msdb.dbo.sysmail_delete_account_sp @account_name='account_smtp_public' EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'account_smtp_public', @email_address = @fromaddress, @description = 'default SMTP Server', @mailserver_name = @smtpserver, @port=@smtpport, @enable_ssl=0 -- 5. Adding the account to the profile EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'sql_alert_profile', @account_name = 'account_smtp_public', @sequence_number =1 ; -- 6. Granting access to the profile to the DatabaseMailUserRole of MSDB EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'sql_alert_profile', @principal_id = 0, @is_default = 1 ; -- 7. Sending Test Mail PRINT ' Sending test mail to ' + @testmailrecipients DECLARE @testsubject varchar(255) SET @testsubject = 'A Test mail from SQL Server ' + @servername EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'sql_alert_profile', @recipients = @testmailrecipients, @body = 'Database Mail Testing... Note that it just tests the reachability of the SMTP server from the SQL Server machine, not that the SQL Agent Job can send mails', @subject = @testsubject; go -- 8. allow agent to use it exec sp_configure 'Agent XPs',1 reconfigure USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1 GO EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1 GO EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'sql_alert_profile' GO -- 9. reset advanced options to default sp_configure 'show advanced options',0 reconfigure go -- OPERATORS -- -- Mailing lists -- USE [msdb] GO -- remove oldies -- repeat as wanted IF EXISTS(SELECT 1 FROM msdb.dbo.sysoperators WHERE name = 'foobar_op' ) exec msdb.dbo.sp_delete_operator @name='foobar_op' -- (re)add ones EXEC msdb.dbo.sp_add_operator @name=N'newfoobarop', @enabled=1, @pager_days=0, @email_address=N'newfoobarop@domain.com' -- EOF Mail Setup -- |