Need help with permissions error on dbmail trigger
Good morning,
I'm stumped on this trigger error and I'm hoping someone can help. Here's the background:
BACKGROUND
Running SQLServer2005 on WinServer 2003 RC2. We use a SQL-based business application that has it's own alert system that uses database mail sucessfully, meaning the built-in emailing functions of the application work and can communicate with the database mail profile and send mail without error.
PROBLEM
I'm trying to write a trigger that will automatically send out an email alert after certain actions are performed in the business application. When I enable the trigger and it tries to run it fails with the following error:
SQLDBCode: 229
Alerts error: SQLSTATE = 4200
Microsoft OLE DB Provider for SQL Server
EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'...
TROUBLESHOOTING
I've seen several articles about DatabaseMailUserRole permissions and have made sure that all users, admin and even guest (for testing) are members of this role. Other than that our setup is pretty vanilla so I'm not sure what else to do.
We only have one large dbase for the app and the fact that the internal emailing fuction works makes me think that the permissions for the msdb are already correct (although I could be wrong)
This problem is driving me crazy so I thank you in advance for any suggestions!
You could try using SQL profiler to see the context being used when trying to send e-mail.
NOTE: If the business application you are using is running under an application role, the root cause of the problem may be a cross-DB context switch using approles (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1312863&SiteID=1) .
Please let us know if you get more information on this issue. We will be glad to help
I hope this information helps,
-Raul Garcia
SDE/T
SQL Server Engine
Raul, thanks for getting back to me. I'm embarrassed to admit I'm not really a database admin so my experience with SQL profiler is limited at best.... I tried to find a KB article or page that shows me how to use it to trace a trigger but I didn't find anything trigger specific. Can you give me a hand on how to do this?
It does appear that the business app is using an application role, at least I see an enabled app role in the dbase's Security->Roles->AppRoles. Would I need to add this app role to the msdb app roles?
Trigger code gets lumped under the RPC events. So, you can see the execution of the trigger under this event.
What you have to boil this down to is the security context that the trigger code is running under when it fires. Some really simple things that you can do are to create a logging table and fire the results of SELECT user_name() into the table. You can also fire the results of SELECT * FROM sys.user_token into an auditing table to see the exact security context the code is running under.
