SQL Server Allow users to access sp_send_dbmail to send email

After setting up an email account / profile in SQL server (http://robbamforth.wordpress.com/2008/10/21/sql-server-2005-database-mail/) you must give each user account permission to access the sp_send_dbmail procedure.

This is done in the following way:

When setting up a new user, select the User Mapping option from the left hand side of the screen.
Scroll down to the MSDB database and tick the box next to it.
Under Database role Membership for:MSDB select DatabaseMailUserRole.
Complete the new user set up as usual.

This user now has permission to access procedures using the sp_send_dbmail command, such as sending database mail:

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = EMAIL_PROFILE’,

    @recipients = test@test.som’,

    @subject = ‘This is a test email’,

    @body = ‘Sample Message’,    

    @body_format = ‘HTML’;

 

 

SQL Server 2005 Database Mail – sp_send_dbmail

STEP 1 Create Email Account in SQL Server:

– CREATE ACCOUNT

EXECUTE msdb.dbo.sysmail_add_account_sp

      @account_name = ‘ACCOUNT1′,

      @description = ‘Mail account for ACCOUNT1 Database Mail’,

      @email_address = ‘EMAIL@ACCOUNT1.co.uk’,

      @display_name = ‘SQL EMAIL’,

@username=‘USER’,

      @password=‘PASS’,

      @mailserver_name = ‘mail.ACCOUNT1.co.uk ‘

 

STEP 2 Creat Email Profile in SQL Server:

– CREATE PROFILE

EXECUTE msdb.dbo.sysmail_add_profile_sp

       @profile_name = ‘ACCOUNT1_PROFILE’,

       @description = ‘Profile used for ACCOUNT1 database mail’

 
STEP 3 Link email account to email profile:

– LINK ACCOUNT TOPROFILE
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp   
@profile_name = ‘ACCOUNT1_PROFILE’,
@account_name = ‘ACCOUNT1′,
@sequence_number = 1

 

STEP 4 Send email:

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = ‘ACCOUNT1_PROFILE’,

    @recipients = ‘email@test.co.uk’,

    @subject = ‘TEST’,

    @body = ‘THIS IS A TEST MESSAGE’,

    @body_format = ‘HTML’;

 

The possible sp_send_dbmail variables arelisted below:

 

sp_send_dbmail [ [ @profile_name = ] ‘profile_name’ ]    
[ , [ @recipients = ] ‘recipients [ ; ...n ]‘ ]    
[ , [ @copy_recipients = ] ‘copy_recipient [ ; ...n ]‘ ]    
[ , [ @blind_copy_recipients = ] ‘blind_copy_recipient [ ; ...n ]‘ ]    
[ , [ @subject = ] ‘subject’ ]    
 [ , [ @body = ] ‘body’ ]    
 [ , [ @body_format = ] ‘body_format’ ]    
[ , [ @importance = ] ‘importance’ ]    
[ , [ @sensitivity = ] ‘sensitivity’ ]    
[ , [ @file_attachments = ] ‘attachment [ ; ...n ]‘ ]    
[ , [ @query = ] ‘query’ ]    
[ , [ @execute_query_database = ] ‘execute_query_database’ ]    
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]    
[ , [ @query_attachment_filename = ] query_attachment_filename ]    
[ , [ @query_result_header = ] query_result_header ]    
[ , [ @query_result_width = ] query_result_width ]    
[ , [ @query_result_separator = ] ‘query_result_separator’ ]    
[ , [ @exclude_query_output = ] exclude_query_output ]    
[ , [ @append_query_error = ] append_query_error ]    
[ , [ @query_no_truncate = ] query_no_truncate ]    
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]