Part of the reason for a reduction in the amount of blogging that I have been doing is that I have tried to expand the amount of information that I put into writing up a topic and instead of blogging those topics, publish them as articles online. My most recent article was just published on SQL Server Central and is titled “Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail.”
A common problem that I have seen online and recently ran into personally, is how to call sp_send_dbmail from a database stored procedure without having to explicitly add user logins as a user in msdb and explicitly grant those logins the DatabaseMailUserRole. In this article I demonstrate how to create a wrapper stored procedure in your database and use a certificate to sign this stored procedure and provide the necessary security rights through the certificate signed procedure to avoid errors such as:
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'.
To read about how to do this, see the article at:
http://www.sqlservercentral.com/articles/Security/68873/