msdb.dbo.sp_send_dbmail does not execute within a stored procedure

Question

I've used the following code to set up deadlock monitoring:

http://weblogs.sqlteam.com/mladenp/archive/2008/07/18/Immediate-deadlock-notifications-without-changing-existing-code.aspx

and it works fine. The problem I have is that msdb.dbo.sp_send_dbmail doesn't seem to work. If I manually execute from my session, It works fine, but from within the stored procedure, the following error occurs:

2011-08-23 16:42:45.28 spid219s The activated proc [dbo].[usp_ProcessNotification] running on queue Wayne.dbo.DeadLockNotificationsQueue output the following: 'Error WHILE receiving Service Broker message FROM queue DeadLockNotificationsQueue. DATABASE Name: Wayne; Error number: 229; Error Message: The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.'

I also ran the following:

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole' ,@membername = '';

with my logon as the membername but it didn't seem to help.

What do I need to do to rectify this?

Thanks.

Wayne.

Answer 1

Martin pointed to the right cause, the EXECUTE AS context under which your activated procedure runs. You can use code signing to grant the required permissions, as shown in Call a procedure in another database from an activated procedure. Or you can simply mark the database Wayne trustworthy:

ALTER DATABASE [Wayne] SET TRUSTWORTHY ON;

The former method is complicated, but is a must in a secure environment. The later method is much easier, but it implies that the dbo of Wayne database can escalate himself to sysadmin privileges. If the security issue is not a concern, you can use the simpler TRUSTWORTHY approach.,