sp_send_dbmail : can I grant execute permission to a role in another database?

Posted on 2013-09-25
Medium Priority
Last Modified: 2013-10-03
I have a trigger set up on an insert in a table in my production database.  When this fires, I want it to send an email if failure conditions are met.  I was getting the standard permission errors with sp_send_dbmail but I finally got it to work by adding the user account to msdb db and placing it in the DatabaseMailUserRole group.

My issue is that I have upwards of a hundred users with new ones coming on all the time.  In the production database, they all belong to their own database role and get assigned to this when new ones are created.

Is it possible to just add that database role in my production db to the msdb database and grant it execute permission so I will no longer have to worry about adding each user and new users as they come on board?  If so, please include the syntax as everything I try does not seem to work.


P.S.  This is on a SQL Server 2008 box.
Question by:CIC Admin
  • 3
  • 2
LVL 28

Accepted Solution

Ryan McCauley earned 2000 total points
ID: 39526240
We had the same situation on one of our servers, with a trigger sending email, but about 150 different possible database users that could be running the connection, so they all needed rights to send mail.

Though it's less than ideal, we actually got around it by granting execute rights to public, meaning that anybody who had a login to the msdb database at all could send mail. We then granted all of our users a login for msdb, but without any explicit rights or role membership - that way, they had no access to msdb database contents, but could still send mail. That was done with:


Open in new window

If you're using Windows/AD logins (rather than SQL logins), you also have the option of granting their AD group rights to execute that stored proc - that way, you don't have to grant rights to everybody on the server, but can still control who has rights to send mail. However, keep in mind that if the trigger is sending mail and your users don't have rights, it will throw an exception and roll back the DML statement the fired the trigger.

Alternatively, you could wrap the actual sending of mail in a BEGIN TRY...CATCH block, so if there's an error sending mail (the user doesn't have permissions), it can log it somewhere and move on, without failing and rolling back the whole transaction.

Author Closing Comment

by:CIC Admin
ID: 39541139
Thank you for your feedback, as well as your additional input.  Barring any other solutions, that should work for our purposes.

We can probably even add code or manual steps when adding new users to the msdb db at the time they are created.  Also, we are using SQL logins for this application so Windows/AD is out.  

I will also implement the BEGIN TRY CATCH code.  Although the trigger is fired hundreds of times per day, the specific set of circumstances that cause the email portion to execute only occurs about once per week.  Even so, the BEGIN TRY CATCH code is something that will make the code more robust.

Thanks again,
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39543720
Another option you might consider is, rather than having the trigger actually send the email, have it dump a record into a queue table of some kind. That way, you can schedule a SQL Agent job on whatever schedule you want to fetch new records and send email as necessary for them. That way, you send mail as the SQL Agent account, rather than the database user itself, so there's no need for elevated permissions (possibly just granting your agent account permission to send mail).

If you are going to stick to sending mail from the trigger directly, the try/catch block is going to be critical so your application user doesn't get an error message that rolls back their transaction.

Author Comment

by:CIC Admin
ID: 39543916
Thanks for the additional viewpoint, Ryan.  I might consider that.  

I selected a trigger because I need to send the email notifying them of their error pretty right after they create their record.  I understand what you are saying about the table but what kind of overhead does it cause on the system if I continuously query that table?  I can probably live with it running every minute or two.  Our server is pretty decent and we have backups and other things that run every hour or so, but nothing running every minute.  I also wonder about filling up the msdb logs and records.  I'm probably just being paranoid, though.

I suppose I can even turn it into something outside the current scope and make a "notifier" app that can send emails to different people on totally unrelated events.  Hmmmm.  Good food for thought.  Thanks!
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39544228
Sure thing. I wouldn't be too concerned about the overhead of querying that table every minute (or even more often) as we do that in a few cases and it doesn't cause a performance issue. Also, you'll have to have some kind of a column in your queue that marks whether a record has been "dealt with" (either a DATETIME or a BIT, most likely) since since you're using SQL 2008, you can set up a filtered index that only includes rows that haven't yet been addressed. That way, you're not querying history, no matter how large that table gets - you're only looking at records that still need your attention (as long as you're filtering your queries properly).

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question