DBMail permission error

I am using DBMail to send an email notification at the completion of service broker process and have been getting the following security error:

Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1

EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

I have given the userID used to run the Service Broker, databasemailuserrole in msdb as per instruction in the msdb. I have also given it execute permission on the sp_send_dbmail but keep getting the error. The service broker is run using a sql login and the dbmail profile is set as public profile. Any suggestions would be much appreciated.

One thing you can do to find additional information on this error is to connect SQL Profiler and run a trace while redoing these steps. When you'll get the permission failure in the trace, you should find additional information telling you what the execution context was at that time. This should help you determine if you have granted EXECUTE permission to the proper principal. If things look as you expected in the trace, take a look at the catalogs as well, to see what permissions are granted on sp_send_mail.

Thanks
Laurentiu

|||

By 'service broker process' I assume you mean an activate procedure. In this case what happens is that you are hitting the 'Extending Database Impersonation under EXECUTE AS context' issue. See this blog entry at http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx

The solution is to either sign the activated procedure or mark the database as trustworthy.

HTH,
~ Remus

|||

Remus,

For test purposes, I assign guest to DatabaseMailUserRole in the msdb and I was able to receive the email successfully.

Can you clarify on your blog statement: "sign the procedure with a server level certificate that has the proper rights needed for the operation in question".

What I did were as follows:

1. create certificate with AUTHORIZATION of the user ID that has sysadmin server role (for testing purpose) in the database of the activated sproc

2. add signature to the activated sproc using the newly created certificate

3. add user ID from #1 to msdb and assign DatabaseMailuserRole role

When I run the service broker job, it still resulted in the permission error. Do I miss any steps ?

Many Thanks,

Welly

|||

You have not granted permissions to the certificate. Here's an example that shows how to sign a stored procedure and how to assign permissions to the signing certificate:

http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx

The owner (who you specified in the AUTHORIZATION clause) does not matter for signing purposes. In your scenario, you would need to grant EXECUTE on sp_send_dbmail.

Thanks
Laurentiu