Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail

  • Comments posted to this topic are about the item Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • That is terrific Jonathan, and to my mind, a great solution generally for elevating the rights of a stored procedure (especially a stored proc that has to use xp_cmdshell or oa_create methods).

    An easy to follow article 10/10 !

  • great article

    no need for it, but into the briefcase it goes in case i'll need it in the future

  • Great article Jonathan. Makes everything clear. Signing a module is very useful in many areas.

  • Hi,

    Really interesting for me also.

    Could that be applied in the case of a CLR stored proc and what dbrole could be used for certified login to allow calling webservices through CLR?

    Currently, I use Trustworthy on for my .net proc to succeed but I'd prefer not to have to do that.

    Thanks in advance

  • You can see how to use a certificate signed assembly on my article Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents[/url] to keep from having to set TRUSTWORTHY ON.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Dumb question...

    What's wrong with using WITH EXECUTE AS? http://www.mssqltips.com/tip.asp?tip=1227

  • johnzabroski (12/17/2009)


    Dumb question...

    What's wrong with using WITH EXECUTE AS? http://www.mssqltips.com/tip.asp?tip=1227%5B/quote%5D

    The stored procedure in the article uses WITH EXECUTE AS. Not sure what you are asking specifically or that you read the article entirely or tested the code in it.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Jonathan Kehayias (12/17/2009)


    The stored procedure in the article uses WITH EXECUTE AS. Not sure what you are asking specifically or that you read the article entirely or tested the code in it.

    I knew it was a dumb question!

    Yeah... I stopped reading right before you got to that point, because I thought you were skipping over a solution I'd used in the past. Wow, your solution is far superior.

  • It's not a stupid question and it is easy to miss even if you are reading through it. I just wasn't sure how to answer the question being asked based on the information provided in the question.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • CREATE PROCEDURE [dbo].[TestSendMail]

    .....

    WITH EXECUTE AS <account with rights to send e-mails>

    Wouldn't it be easier that way?

  • simon.murin (12/17/2009)


    CREATE PROCEDURE [dbo].[TestSendMail]

    .....

    WITH EXECUTE AS <account with rights to send e-mails>

    Wouldn't it be easier that way?

    Have you tried it without TRUSTWORTHY ON? It won't work for a number of reasons. First the possible values for the EXECUTE AS clause are

    EXECUTE AS Clause (Transact-SQL)


    Functions (except inline table-valued functions), Stored Procedures, and DML Triggers

    { EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

    You would have to specify a login_name, not a user_name because this stored procedure is in a User Database and the permissions needed to execute sp_send_dbmail are needed in msdb. The database user in a user database has no rights or permissions in another database, the login associated with that user does. TRUSTWORTHY ON would be required to make that work, which has additional ramifications security wise.

    From the Article....


    This allows for Impersonation across databases to gain access to resources in other databases on the server. It is not a secure method of solving the problem since having TRUSTWORTHY ON allows for other side effects. If the database owner is a sysadmin, then TRUSTWORTHY ON provides full access to the server. This flag is also set OFF whenever a database is attached or restored to SQL Server. This is an easy to miss item that can become problematic in the worst possible times, like during disaster recovery.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Great article I found after searching for long time in Internet about impersonation and cross database accessing.

    I got one simple solution perticularly for database mail.

    Simply give execute permission on the sp "sp_send_dbmail" to the role "public" in the database "msdb".

    Any call to this sp from other database/sp/function/.. with impersination (or without impersination I hope) will be take care.

    You can get rid of the most annoying error "The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'"

  • Uma-419424 (1/8/2010)


    Simply give execute permission on the sp "sp_send_dbmail" to the role "public" in the database "msdb".

    That would violate Audit rules at my company, and most publicly held companies as well. In fact we have a script that locks down the public role further than it is at installation. If you develop software for sale, you have to keep things like Audit's in mind or you can severely limit your customer base.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Thanks for the great read.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply