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
If I connect in SSMS as
TheUser and execute
TheNotificationProcedure, everything works and emails go out.
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:
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.
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?