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:
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?
Here is a script to duplicate the above permissions issue without any of the module signing (which gives the same "guest" trace) when going through service broker:
Setup:
--REPLACE EMAIL, and db_mail profile
--@profile_name = 'Test db mail profile',
--@recipients = 'test@test.test',
use master;
GO
IF EXISTS(select * FROM sys.databases where name='http://dba.stackexchange.com/questions/166033')
BEGIN
ALTER DATABASE [http://dba.stackexchange.com/questions/166033] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [http://dba.stackexchange.com/questions/166033] SET ONLINE WITH ROLLBACK IMMEDIATE;
DROP DATABASE [http://dba.stackexchange.com/questions/166033];
END
CREATE DATABASE [http://dba.stackexchange.com/questions/166033];
GO
IF EXISTS(select * FROM sys.server_principals WHERE name = 'TheUser' AND type_desc='SQL_LOGIN') DROP LOGIN TheUser;
CREATE LOGIN [TheUser] WITH PASSWORD=N'jL839lIFKttcm3cNuk1WUazfk5lS76RKMscZ01UdFkI='
, DEFAULT_DATABASE=[http://dba.stackexchange.com/questions/166033]
, DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
use [msdb];
GO
IF (NOT EXISTS(select * FROM sys.database_principals WHERE name = 'TheUser')) CREATE USER [TheUser] FOR LOGIN [TheUser] WITH DEFAULT_SCHEMA=[dbo];
exec sp_addrolemember 'DatabaseMailUserRole', 'TheUser';
GO
use [http://dba.stackexchange.com/questions/166033];
GO
CREATE USER [TheUser] FOR LOGIN [TheUser] WITH DEFAULT_SCHEMA=[dbo]
GO
CREATE SCHEMA [Blah] AUTHORIZATION dbo;
GO
CREATE QUEUE [Blah].[SourceQueue];
GO
CREATE SERVICE [//FromService]
AUTHORIZATION [dbo]
ON QUEUE [Blah].[SourceQueue];
GO
CREATE MESSAGE TYPE [//TestMessage]
AUTHORIZATION [dbo]
VALIDATION = NONE;
GO
CREATE CONTRACT [//ServiceContract]
AUTHORIZATION [dbo]
([//TestMessage] SENT BY INITIATOR);
GO
CREATE PROCEDURE [Blah].[SendMessage]
AS
DECLARE @message varchar(50),
@conversationHandle UNIQUEIDENTIFIER
SET @message = 'Test Message Content';
-- Begin the dialog.
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [//FromService]
TO SERVICE '//ToService'
ON CONTRACT [//ServiceContract]
WITH ENCRYPTION = OFF;
-- Send the message on the dialog.
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [//TestMessage]
(@message) ;
END CONVERSATION @conversationHandle ;
GO
CREATE PROCEDURE [dbo].[TheNotificationProcedure]
AS
PRINT 'DEBUG - Entering [dbo].[TheNotificationProcedure]'
-- Send notification
PRINT 'DEBUG - [dbo].[TheNotificationProcedure] - PRIOR TO msdb.dbo.sp_send_dbmail'
declare @log nvarchar(max) = '';
select @log = @log + 'name: ' + name + ' ' + 'type: ' + type + ' usage: ' + usage + ' || ' FROM sys.login_token
print @log
declare @mailitem_id int;
--exec [msdb].[dbo].[WRAP__sp_send_dbmail]
exec [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'Test db mail profile',
@recipients = 'test@test.test', --@Recipient,
@subject = 'Testing sp_send_dbmail', --@NotificationSubject,
@body = 'Testing sp_sdend_dbmail from service broker', --@NotificationBody,
@exclude_query_output = 1,
@mailitem_id = @mailitem_id OUTPUT
PRINT 'DEBUG - [dbo].[TheNotificationProcedure] - AFTER msdb.dbo.sp_send_dbmail'
GO
CREATE PROCEDURE [Blah].[TestMessageHandler]
AS
--has other logic that eventully calls notification
EXECUTE [dbo].[TheNotificationProcedure]
GO
CREATE PROCEDURE [Blah].[TheQueueProcedure]
AS
--Service Broker variables
DECLARE @conversation_handle UNIQUEIDENTIFIER,
@conversation_group_id UNIQUEIDENTIFIER,
@message_body varchar(255),
@message_type_name NVARCHAR(256),
@dialog UNIQUEIDENTIFIER,
@RowsReceived int
PRINT 'Start'
WHILE (1 = 1)
BEGIN
-- Get next conversation group.
WAITFOR(
GET CONVERSATION GROUP @conversation_group_id FROM [Blah].[TheQueue]),
TIMEOUT 500 ;
-- If there are no more conversation groups, roll back the
-- transaction and break out of the outermost WHILE loop.
IF @conversation_group_id IS NULL
BEGIN
BREAK ;
END ;
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
PRINT 'Get Message'
; RECEIVE TOP (1)
@dialog = conversation_handle,
@message_type_name=message_type_name,
@message_body=message_body
FROM [Blah].[TheQueue]
WHERE conversation_group_id = @conversation_group_id ;
SET @RowsReceived = @@ROWCOUNT
PRINT 'Queue Read: ' + ISNULL(@message_body, '<NULL>')
PRINT '@RowsReceived: ' + CAST(@RowsReceived as varchar(200))
IF (@RowsReceived = 0)
BEGIN
BREAK ;
END ;
PRINT 'Deal with Message'
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + cast(@dialog as nvarchar(40)) ;
END CONVERSATION @dialog ;
END ;
IF (@message_type_name = '//TestMessage')
BEGIN
print 'Have //TestMessage: ' + @message_body
exec [Blah].[TestMessageHandler];
END
COMMIT TRANSACTION;
END
END
RETURN
GO
CREATE QUEUE [Blah].[TheQueue]
WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = [Blah].[TheQueueProcedure], MAX_QUEUE_READERS = 1, EXECUTE AS N'TheUser');
GO
CREATE SERVICE [//ToService]
AUTHORIZATION [dbo]
ON QUEUE [Blah].[TheQueue]
([//ServiceContract]);
GO
GRANT EXECUTE ON [Blah].[TheQueueProcedure] TO [TheUser];
GO
Then to kick everything off:
--kick everything off
EXEC [Blah].[SendMessage];
GO
--read results from error log
--(might need to execute once or twice to get results - because service broker is asynchronous)
declare @sqlErrorLog table (LogDate datetime, ProcessInfo nvarchar(max), Text nvarchar(max));
INSERT INTO @sqlErrorLog EXEC xp_ReadErrorLog
SELECT * FROM @sqlErrorLog
WHERE LogDate >= DATEADD(SECOND, -15, GETDATE()) AND Text NOT LIKE 'CHECKDB%' AND Text NOT LIKE 'Starting up database ''upgrade%' AND Text NOT LIKE '%upgrade%information%' AND TEXT <> 'Error: 9001, Severity: 21, State: 1.'
ORDER BY LogDate
