I recently had to setup Database Mail on dozens of SQL Server instances. Rather than perform this tedious task using the SSMS GUI, I developed a script that saved me a lot of time which I'm sharing here.
My needs were simple so I only needed a single SMTP account and profile. I decided to make the profile the default public one so that all msdb users would use this profile unless a different sp_send_dbmail @profile value was explicitly specified. You might want to extend this script if you need other accounts/profiles, such as separate ones for administrative alerts or user reports.
Setup Script
Below is the template script I used for my task. The sysmail_add_account_sp @username and @password parameters might be required depending on your SMTP server authentication and you will of course need to customize the mail server name and addresses for your environment.
-- Enable Database Mail for this instance
EXECUTE sp_configure 'show advanced', 1;
RECONFIGURE;
EXECUTE sp_configure 'Database Mail XPs',1;
RECONFIGURE;
GO
-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Primary Account',
@description = 'Account used by all mail profiles.',
@email_address = 'xyz@yahoo.com',
@replyto_address = 'abc@gmail.com',
@display_name = 'Database Mail',
@mailserver_name = 'smtp.gmail.com';
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Default Public Profile',
@description = 'Default public profile for all users';
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Default Public Profile',
@account_name = 'Primary Account',
@sequence_number = 1;
-- Grant access to the profile to all msdb database users
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Default Public Profile',
@principal_name = 'public',
@is_default = 1;
GO
--send a test email
EXECUTE msdb.dbo.sp_send_dbmail
@subject = 'Test Database Mail Message',
@recipients = 'xyz@yahoo.com',
@query = 'SELECT @@SERVERNAME';
GO
|
Comments