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

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.

Thanks,
Kent

P.S.  This is on a SQL Server 2008 box.
LVL 5
CIC AdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan McCauleyEnterprise Analytics ManagerCommented:
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:

GRANT EXECUTE ON sp_send_dbmail TO PUBLIC

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CIC AdminAuthor Commented:
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,
Kent
0
Ryan McCauleyEnterprise Analytics ManagerCommented:
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.
0
CIC AdminAuthor Commented:
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!
0
Ryan McCauleyEnterprise Analytics ManagerCommented:
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).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.