New Article on SQL Server Central: Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail

jmkehayias — Thu, 17 Dec 2009 07:58:53 GMT

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/

Search results matching tags 'Database Administration', 'Articles', and 'Security'

New Article on SQL Server Central: Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail