CIC Admin
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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!
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!
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).
ASKER
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