I am creating a trigger for insert into a table. At the end of the trigger, I want to send an email.
The problem is the user who is calling the stored procedure that is doing the insert, does not have permissions on sp_send_dbmail
So when the trigger fires because of the insert, the sp_send_dbmail call is failing due to permissions.
Here is how I got this to work:
- create a wrapper procedure in msdb that calls sp_send_dbmail.
- add the user who will be calling the wrapper procedure, into msdb and grant them execute permissions on the new wrapper
- set the databasemail profile that is being used, as the global default
bingo - bango, it works
Thanks for your sharing. Your post will help others have concern with these topics. Thanks for your effort again.
TechNet Community Support
Microsoft is conducting an online survey to understand your opinion of the Msdn Web site. If you choose to participate, the online survey will be presented to you when you leave the Msdn Web site.
Would you like to participate?