Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    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]

  • C# Gnu

    SSCertifiable

    Points: 5973

    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 !

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    great article

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

  • Jack Corbett

    SSC Guru

    Points: 184296

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


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck out these links on how to get faster and more accurate answers: Forum Etiquette: How to post data/code on a forum to get the best helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • dan77eu

    SSC Journeyman

    Points: 86

    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

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    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]

  • johnzabroski

    SSCrazy

    Points: 2355

    Dumb question...

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

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    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]

  • johnzabroski

    SSCrazy

    Points: 2355

    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.

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    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]

  • simon.murin

    SSC-Addicted

    Points: 449

    CREATE PROCEDURE [dbo].[TestSendMail]

    .....

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

    Wouldn't it be easier that way?

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    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]

  • SQLEnthu

    SSC Journeyman

    Points: 94

    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'"

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    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]

  • SQLRNNR

    SSC Guru

    Points: 281079

    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