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 12»»

Using the sp_Send_DBMail SP in msdb Within A Best Practices approach Expand / Collapse
Author
Message
Posted Thursday, November 19, 2009 10:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, March 10, 2014 1:21 PM
Points: 885, Visits: 1,526
NOTE: Because this falls under several Forums sections from Development to Security to Strategy I decided to drop it under the GENERAL section.

NOTE 2: I hope I’m not beating a dead horse with this one b/c I know there are numerous posts on the internet on this with a variety of replies but when I search the SQL Server Central Forums I get zero hits so apologies if the answer is already listed here somewhere.


What if any is the Best Practices approach to using the sp_send_dbmail STORED PROC in the MSDB database on SQL Server (2005/2008)?? Currently I have a custom SP that among a few other tasks will call this MSDB Stored Procedure to send an email alert that is raised by a Table Update Trigger. I am of course getting the same PERMISSION DENIED error msg that every other SQL Server users get’s when they have not explicitly done some kind of change to security for this SP so that it can be called.

The error of course comes from the fact that you have to be of a certain Role or have specific permissions for this SP in the MSDB database. That makes sense in that it follows the whole security paradigm in SQL Server however this particular SP is in my opinion not the same as any other because of what it does. Now I’ve seen various suggestions (see below List) but what I’d like to know is if any of you have any suggestions to add or thoughts comments on the ones below as well as on any that others add, specifically commenting on what you feel is a BEST PRACTICES approach to this common problem.

Thanks to all


Options for dealing with the Permission Denied error received when using the MSDB Stored procedure sp_Send_DBMail.

1) SQL Email Job – Create a table to store Email msgs/alerts and have a job check the table for new items and when it finds any, use the job to send these out thru the sp_Send_DBMail SP. By setting up the job and giving the Job owner permissions to the Stored Procedure in the MSDB database you can allow for sending emails via SQL Server.

PROS: It works. While not easy it is also not too terribly hard to implement. Minimizes potential Security issues
CONS: Unless the job runs all the time it’s not live or real time.

2) Run with the EXECUTE AS option - Setup the SP to run under a security context that has access to execute the SP

PROS: Easy to implement
CONS: May not always work (at least that’s what I read in various posts); Possible Security concerns raised

3) Grant Security Access Users - Explicitly grant EXECUTE priveledges to the sp_Send_DBMail SP to all users/roles that will call the SP

PROS: If you use Roles then so long as any new users are added to the role you need only set this up once for it to run for existing and new users alike.
CONS: Not easy to implement unless you have a few users; Raises serious security concerns depending on your environment.



Kindest Regards,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.
Post #821810
Posted Thursday, November 19, 2009 2:16 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, March 10, 2014 1:21 PM
Points: 885, Visits: 1,526
UPDATE:

Well it appears Option 2 'EXECUTE AS' is no't reeally of any use by iteslf because it doesn't appear to work. In my testing, the only values that work with EXECUTE AS as CALLER and OWNER and I can't use OWNER because it aparently doesn't work with the implied or defualt 'dbo' database owner.

In our DB all objects are owned by the dbo or by the Login that is the Database Owner. When I set this to run as OWNER it fails. When I set to to CALLER it works but the login I am running the SP (the one in my DB that calls sp_Send_DBMail) has to have been given explict access to the msdb DB and to that STored Procedure. This means that every distinct login in my DB must also be granted access to the msdb database since you can't assign DB Roles (from other DB's) to msdb and there are no seevre level custom roles.

This is a reall pain in the arse. Is it just me or does it seem like Microsoft made the DB Mail functionality a nightmare to use?


Kindest Regards,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.
Post #821959
Posted Friday, November 20, 2009 6:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 10,910, Visits: 12,545
There is a role, DatabaseMailUser, in msdb that you can put users in.

I'm a fan of option 1 in your case, even though Database Mail is asynchronous, I just don't like sending mail from a trigger, you could do it.

I'm doing some research on issues like this using signed modules. I'll have to see if it will work here.




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 #822329
Posted Friday, November 20, 2009 12:24 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, March 10, 2014 1:21 PM
Points: 885, Visits: 1,526
Jack Corbett (11/20/2009)
There is a role, DatabaseMailUser, in msdb that you can put users in.

I'm a fan of option 1 in your case, even though Database Mail is asynchronous, I just don't like sending mail from a trigger, you could do it.

I'm doing some research on issues like this using signed modules. I'll have to see if it will work here.


Thanks for the tip on the role Jack. I thought I had tried using this already and ity didn't work but I also may just have it mixed up with something else. Eve have one of those times where you've tried so many various methods you can't remember specifics on each?

I know that Option #1 is a working option and seems to have a lot of backing among users from internet posts I've seen on this but it just also seems like more of a hack then a solution. Its not a bad hack but still a hack. Any idea if this issue is fixed in 2008?


Kindest Regards,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.
Post #822633
Posted Friday, November 20, 2009 12:44 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
In a trigger, I'd definitely be more inclined towards inserting into a queue and then having a job send the e-mails. Schedule for once a minute, and you're not getting too far from "realtime".

I use this one a lot in things like my maintenance procs. They send me alerts about a variety of things going on in the databases that I like to know about. With those, I send directly from the procs. But those are running under DBA authority, and need to do so, so there's no permissions issue there.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #822648
Posted Friday, November 20, 2009 2:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 10,910, Visits: 12,545
YSLGuru (11/20/2009)
Jack Corbett (11/20/2009)
There is a role, DatabaseMailUser, in msdb that you can put users in.

I'm a fan of option 1 in your case, even though Database Mail is asynchronous, I just don't like sending mail from a trigger, you could do it.

I'm doing some research on issues like this using signed modules. I'll have to see if it will work here.


Thanks for the tip on the role Jack. I thought I had tried using this already and ity didn't work but I also may just have it mixed up with something else. Eve have one of those times where you've tried so many various methods you can't remember specifics on each?

I know that Option #1 is a working option and seems to have a lot of backing among users from internet posts I've seen on this but it just also seems like more of a hack then a solution. Its not a bad hack but still a hack. Any idea if this issue is fixed in 2008?


Yup, been there when you can't remember if you tried everything or not.

Option 1 is the option of choice because SQL Mail was/is synchronous and should never have been in a trigger, but Database Mail is asynchronous so it can be used in a trigger, but I still prefer not to it (old habits and prejudices die hard). This has not been changed in 2008, you still need rights on Database Mail to send mail.




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 #822712
Posted Monday, November 23, 2009 10:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, March 10, 2014 1:21 PM
Points: 885, Visits: 1,526
GSquared / Jack Corbett,

Thansk to both of you for chipping in on this one. I'm suprised the response wasn't larger; maybe I'm one of the few who have to deal with this. Do most not even use SQL Server's email capabilities?

Do either of you have any outline code (T-SQL) for a Message Queue Job you;d be willing to share?


Thanks

Ed


Kindest Regards,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.
Post #823401
Posted Tuesday, November 24, 2009 6:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
One way to hack around this problem is to turn TRUSTWORTHY ON for the database that has your wrapper stored procedure in it. However, the best way to do this is to use EXECUTE AS OWNER, and sign the procedure with a Certificate. To do this requires the following steps:

First create the stored procedure.
Then create a certificate in the database with a strong password.
Backup the certificate to disk.
Restore the certificate in master and create a login from the certificate.
Grant Authenticate Server to the login.
Add a database user in msdb for the certificate login.
Then add the DatabaseMailUserRole to the msdb user for the certificate login.
Finally sign the stored procedure in your database with the certificate.

A full repro for this is below:

CREATE DATABASE TestDBMail
GO

USE [TestDBMail]
GO

CREATE PROCEDURE [dbo].[TestSendMail]
(
@To NVARCHAR(1000),
@Subject NVARCHAR(100),
@Body NVARCHAR(MAX)
)
WITH EXECUTE AS OWNER
AS
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'Database Mail Profile',
@recipients = @To,
@subject = @Subject,
@body = @Body
END
GO

-- This should fail
EXECUTE [dbo].[TestSendMail] 'someemail@domain.com', 'test', 'body'

-- Create a certificate to sign stored procedures with
CREATE CERTIFICATE [DBMailCertificate]
ENCRYPTION BY PASSWORD = '$tr0ngp@$$w0rd'
WITH SUBJECT = 'Certificate for signing TestSendMail Stored Procedure';
GO

-- Backup certificate so it can be create in master database
BACKUP CERTIFICATE [DBMailCertificate]
TO FILE = 'd:\Backup\DBMailCertificate.CER';
GO

-- Add Certificate to Master Database
USE [master]
GO
CREATE CERTIFICATE [DBMailCertificate]
FROM FILE = 'd:\Backup\DBMailCertificate.CER';
GO

-- Create a login from the certificate
CREATE LOGIN [DBMailLogin]
FROM CERTIFICATE [DBMailCertificate];
GO

-- The Login must have Authenticate Sever to access server scoped system tables
-- per http://msdn.microsoft.com/en-us/library/ms190785.aspx
GRANT AUTHENTICATE SERVER TO [DBMailLogin]
GO

-- Create a MSDB User for the Login
USE [msdb]
GO
CREATE USER [DBMailLogin] FROM LOGIN [DBMailLogin]
GO

-- Add msdb login/user to the DatabaseMailUserRole
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = 'DBMailLogin';
GO

USE [TestDBMail]
GO

-- Sign the procedure with the certificate's private key
ADD SIGNATURE TO OBJECT::[TestSendMail]
BY CERTIFICATE [DBMailCertificate]
WITH PASSWORD = '$tr0ngp@$$w0rd';
GO

-- This will succeed
EXECUTE [dbo].[TestSendMail] 'someemail@domain.com', 'test', 'body'

/*
-- Cleanup
USE [msdb]
GO
DROP USER [DBMailLogin]
GO
USE [master]
GO
DROP LOGIN [DBMailLogin]
DROP CERTIFICATE [DBMailCertificate]
DROP DATABASE [TestDBMail]

-- Delete the certificate backup from disk

*/

This will allow you to use permission chaining the database that the wrapper procedure exists in while enforcing best security practices in design.


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 #824246
Posted Tuesday, November 24, 2009 6:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 10,910, Visits: 12,545
Great Jonathan. That's the blog post I've been working on for a couple weeks as I figure it all out



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 #824248
Posted Tuesday, November 24, 2009 7:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
Jack Corbett (11/24/2009)
Great Jonathan. That's the blog post I've been working on for a couple weeks as I figure it all out


That's the first article I just sumbitted to SSC as a part of a series I have been working on for almost a month. Look for some really fun stuff here soon.


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 #824250
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse