Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail Expand / Collapse
Author
Message
Posted Wednesday, December 16, 2009 11:43 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 1:10 PM
Points: 1,681, Visits: 1,793
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
Post #835572
Posted Wednesday, December 16, 2009 11:48 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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 !





C# Gnu
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #835573
Posted Thursday, December 17, 2009 6:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:08 PM
Points: 1,414, Visits: 4,540
great article

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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #835633
Posted Thursday, December 17, 2009 8:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:37 PM
Points: 10,206, Visits: 13,152
Great article Jonathan. Makes everything clear. Signing a module is very useful in many areas.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check 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 help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #835731
Posted Thursday, December 17, 2009 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 2, 2012 10:12 AM
Points: 2, Visits: 46
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
Post #835799
Posted Thursday, December 17, 2009 10:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 1:10 PM
Points: 1,681, Visits: 1,793
You can see how to use a certificate signed assembly on my article Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents 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
Post #835827
Posted Thursday, December 17, 2009 11:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 6, 2014 1:24 PM
Points: 49, Visits: 189
Dumb question...

What's wrong with using WITH EXECUTE AS? http://www.mssqltips.com/tip.asp?tip=1227
Post #835862
Posted Thursday, December 17, 2009 11:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 1:10 PM
Points: 1,681, Visits: 1,793
johnzabroski (12/17/2009)
Dumb question...

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


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
Post #835874
Posted Thursday, December 17, 2009 12:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 6, 2014 1:24 PM
Points: 49, Visits: 189
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.
Post #835885
Posted Thursday, December 17, 2009 1:37 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 1:10 PM
Points: 1,681, Visits: 1,793
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
Post #835965
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse