THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

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

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/

Published Thursday, December 17, 2009 3:58 AM by Jonathan Kehayias

Comments

 

Remus Rusanu said:

The idea to sign the procedure is a good one, but you shouldn't use a login in this case. Creating a certificate derived login is necessary only when the signed code needs server level permissions, like select from a server level view (eg. http://rusanu.com/2006/03/01/signing-an-activated-procedure/). If the needed permissions are in another database, then the solution is to import the certificate in the target database, create a certificate derived user, grant impersonate to the user (so that the execute as context is trusted) and finally grant the needed permission (or add to the needed role), as in http://rusanu.com/2006/03/07/call-a-procedure-in-another-database-from-an-activated-procedure/. Also is important to call out that the private key is usually dropped so that the certificate can never be used again to sign something else, which adds trust in the signature. This is how mssqlsystemresource code is signed.

December 17, 2009 11:40 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement