error fix: The EXECUTE permission was denied on the object sp_send_dbmail, database 'msdb'

Today I am in a mood to fix the error ;)

After reading my article at http://www.sqlhub.com/2009/04/email-from-insert-trigger-with.html one of my team members has tried to send an email from our production server and faced the error:

The EXECUTE permission was denied on the object ‘sp_send_dbmail’, database 'msdb'

He comes to me for the solution as I am his handy reference than why he should even go to google J

Anyway, the main cause of this error is either your user does not exist in msdb database or it is not a member of DatabaseMailUserRole. How can we fix this?

If you have user exist in msdb database than simply run following script.

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = 'ritesh'

If your user doesn’t exist in msdb database than first add user to msdb and then try to execute above command like:

use msdb CREATE User[ritesh] FOR LOGIN [ritesh];

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = 'ritesh'

Hope this helps!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com