To prevent anyone from sending arbitrary emails, Database Mail is guarded by the database role DatabaseMailUserRole. Users who are not the member of DatabaseMailUserRole cannot send emails except sysadmin who has all privileges.
For example, an unauthorized user sends email by running sp_send_dbmail.
EXEC sp_send_dbmail @profile_name=’MailProfile1′, @recipients=’email@example.com’, @body=’test message’, @subject=’Ignore this message’
The user will encounter the error message below.
Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
The EXECUTE permission was denied on the object ‘sp_send_dbmail’, database ‘msdb’, schema ‘dbo’.
Not only sp_send_dbmail, but also the following Database Mail SPs and views will be bounced if the user is not the member of DatabaseMailUserRole.
If the user is allowed to run sp_send_dbmail and those operations above, you can add him or her to DatabaseMailUserRole with SQL Server Management Studio as follows.
Or you can run sp_addrolememeber below.
More information about DatabaseMailUserRole is included in troubleshooting of Database Mail in MSDN document.
<END OF POST>
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm