Using a Certificate Signed Stored Procedure to Execute sp_send

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jonathan Kehayias » Using a Certificate Signed Stored Procedure...

31 posts, Page 1 of 4

1 2 3 4 »»»

Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail

Rate Topic

Display Mode

Topic Options

Author

Message

Jonathan Kehayias

Posted Wednesday, December 16, 2009 11:43 PM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

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

C# Gnu

Posted Wednesday, December 16, 2009 11:48 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:35 AM
Points: 224, Visits: 629

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
____________________________________________________

Excel Database Tasks V2 ..
Multi user data edit Task based solution - Excel with SQL Server / Access / MySQL ..

Version 2.5 released - See Blog

Post #835573

alen teplitsky

Posted Thursday, December 17, 2009 6:54 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:15 PM
Points: 1,409, Visits: 4,509

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

Jack Corbett

Posted Thursday, December 17, 2009 8:42 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

dan77eu

Posted Thursday, December 17, 2009 10:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 02, 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

Jonathan Kehayias

Posted Thursday, December 17, 2009 10:53 AM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

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

johnzabroski

Posted Thursday, December 17, 2009 11:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 7:57 AM
Points: 47, Visits: 183

Dumb question...

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

Post #835862

Jonathan Kehayias

Posted Thursday, December 17, 2009 11:37 AM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

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

johnzabroski

Posted Thursday, December 17, 2009 12:01 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 7:57 AM
Points: 47, Visits: 183

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

Jonathan Kehayias

Posted Thursday, December 17, 2009 1:37 PM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

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 »

31 posts, Page 1 of 4

1 2 3 4 »»»

Permissions