#StackBounty: #sql-server #sql-server-2008-r2 #permissions #service-broker #signature Can't use msdb.dbo.sp_send_dbmail when in ser…

Bounty: 150

I have a procedure TheNotificationProcedure that makes a cross-database call to msdb.dbo.sp_send_dbmail.

It gets invoked (indirectly) from a service broker queue:

CREATE QUEUE [Blah].[TheQueue]
WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = [Blah].[TheQueueProcedure], 
MAX_QUEUE_READERS = 1, EXECUTE AS N'TheUser');

TheQueueProcedure eventually calls TheNotificationProcedure

If I connect in SSMS as TheUser and execute TheNotificationProcedure, everything works and emails go out.

However, when TheNotificationProcedure is invoked as a result of a message arriving on the queue, it fails as unable to access the msdb procedure.

I’ve tried everything I can think of, including creating my own procedure in msdb that wraps sp_send_dbmail and signing both my dbmail wrapper and TheNotificationProcedure with the same certiface, and ensure the certificate user in msdb is a member of “DatabaseMailUserRole”.

Finally, after doing many more detailed traces, I eventually noticed the following:

Service Broker Trace

That is even though the service broker is executing under the login of TheUser, for some reason, it is executing under the database user of guest, which I suspect at least partially explains my permissions issues.

The login TheUser is also mapped to a user in msdb called TheUser – it is certainly not mapped to guest.

So why is it being executed as guest in msdb when going through service broker?

I need to avoid marking the database as Trustworthy. I was hoping that by signing the procedures (e.g. http://www.sommarskog.se/grantperm.html) I could get permissions to transfer across the database – does execute as negate any permissions that would typically be associated via the certificate user?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.