Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail

Question

Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail

Jonathan Kehayias

One Orange Chip

Points: 26778
More actions
December 16, 2009 at 11:43 pm

#217706

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]

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

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

C# Gnu SSCertifiable Points: 5973 More actions · Answer 1

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 : efficient business data processing
Version 3.3 released February 2015

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

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: 184394 More actions · Answer 3

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

Jack Corbett
Consultant - Straight Path Solutions
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

dan77eu SSC Journeyman Points: 86 More actions · Answer 4

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: 26778 More actions · Answer 5

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.

johnzabroski SSCrazy Points: 2355 More actions · Answer 6

Dumb question...

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

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.

johnzabroski SSCrazy Points: 2355 More actions · Answer 8

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: 26778 More actions · Answer 9

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.

simon.murin SSC-Addicted Points: 449 More actions · Answer 10

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: 26778 More actions · Answer 11

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.

SQLEnthu SSC Journeyman Points: 94 More actions · Answer 12

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: 26778 More actions · Answer 13

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

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