Problems with Permissions error on dbase mail trigger

[ReK410]

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!

 

[ptheriault]

I've run into this problem too. Did you add the user account to the msdb database? Then add them to the role DatabaseMailUserRole?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ReK410]

I'm pretty sure that I've done the permissions correctly. Our business app database is called "P21" and the users list has a few system accounts (for crystal reports, reporting apps, etc..) and the user accounts which are done by groups ("MD Users", "VA Users", "PA Users")

I added those same user groups to the MSDB users and the DatabaseMailUserRole... at least I think I did that correctly, I've attached a screenshot of the setup here - does this look right to you?

http://img.photobucket.com/albums/v308/rek410/Untitled.jpg