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

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:

E开发者_如何转开发XEC 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.

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.,

You can create a procedure in msdb that calls sp_send_dbmail, then grant execute to public on the calling procedure, as suggested by gbn: How to execute sp_send_dbmail while limiting permissions. It is not required to add users to msdb to run the procedure. Obviously, this allows anyone to send mail, subject to procedural logic.