/*==========================================================================

-- Purpose: Used to create Database Mail in SQL Server 2005

-- Author: Carolyn Richardson

-- Date: 24/09/2007

-- ***Important***

Replace servername with actual servername to make it clear re which server is sending the mail, also alter the mail server details.

==========================================================================*/

--Reconfigure the server to allow email

use master

go

sp_configure'show advanced options',1

go

reconfigurewith override

go

sp_configure'Database Mail XPs',1

reconfigure

go

/*Add Service Account Permission

As an extra security feature SQL Server 2005 uses a role with specific permissions in the MSDB database, not even system administrators can use the mail feature without being a member of this role. So you need to either add the specific account that runs SQL Server to this role, or add all administers:-

*/

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name ='ServerNameMailAccount',

@description ='Mail account for Database Mail',

@email_address ='ServerName@address.com',

@display_name ='ServerNameMailAccount',

@use_default_credentials = 0,

@mailserver_name ='smtp.domain.net' --alter mailserver details

/*

If you look at the propertities you may want to altert the anonymous login to SQL Server authentication.

Create Mail Profile

The next component of the configuration requires the creation of a Mail profile.

We are going to create "ServernamMailProfile" using the sysmail_add_profile procedure to create a Database Mail profile.

For help on options use sp_helptext sysmail_add_profile_sp

*/

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name ='ServerNameMailProfile',

@description ='Profile used for database mail'

/*

Add Account Profile

Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account using the Database Mail profile you created in last step

For help on options use sp_helptext sysmail_add_profileaccount_sp

*/

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name ='ServerNameMailProfile',

@account_name ='ServerNameMailAccount',

@sequence_number = 1

/*Set default Profile

Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.

For help on options use sysmail_add_principalprofile_sp

*/

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name ='ServerNameMailProfile',

@principal_name ='public',

@is_default = 1 ;

/*To send a test email from SQL Server. Execute the statement below.*/

declare @body1 varchar(100)

set @body1 ='Server :'+@@servername+' My First Database Email '

EXEC msdb.dbo.sp_send_dbmail @recipients='email@address.com', --change to your email address

@subject ='My Mail Test',

@body = @body1,

@body_format ='HTML';

To set up SQL Server Agent Mail to use Database Mail