Sql-Server
在服務代理中無法使用 msdb.dbo.sp_send_dbmail - 作為來賓執行？
我有一個
TheNotificationProcedure對 msdb.dbo.sp_send_dbmail 進行跨數據庫呼叫的過程。它從服務代理隊列中（間接）呼叫：
CREATE QUEUE [Blah].[TheQueue] WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = [Blah].[TheQueueProcedure], MAX_QUEUE_READERS = 1, EXECUTE AS N'TheUser');
TheQueueProcedure最終呼叫TheNotificationProcedure如果我在 SSMS 中連接
TheUser並執行TheNotificationProcedure，一切正常，電子郵件就會消失。但是，當
TheNotificationProcedure由於消息到達隊列而被呼叫時，它會因為無法訪問 msdb 過程而失敗。我已經嘗試了所有我能想到的方法，包括在 msdb 中創建自己的過程來包裝 sp_send_dbmail，並
TheNotificationProcedure使用相同的證書對我的 dbmail 包裝器進行簽名，並確保 msdb 中的證書使用者是“DatabaseMailUserRole”的成員。最後，在做了許多更詳細的跟踪之後，我最終注意到了以下幾點：
即使服務代理在 的登錄名下執行
TheUser，出於某種原因，它也在 的數據庫使用者下執行guest，我懷疑這至少部分解釋了我的權限問題。登錄
TheUser名也映射到msdb 中稱為的使用者TheUser- 它當然沒有映射到 guest。那麼為什麼在通過服務代理時它在 msdb 中作為訪客執行呢？
我需要避免將數據庫標記為
Trustworthy. 我希望通過簽署程序（例如http://www.sommarskog.se/grantperm.html）我可以獲得跨數據庫傳輸的權限 - 是否execute as否定通常通過證書使用者關聯的任何權限？這是一個腳本，用於在通過服務代理時複製上述權限問題而無需任何模組簽名（提供相同的“來賓”跟踪）：
設置：
--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然後開始一切：
--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
我需要避免將數據庫標記為
Trustworthy.這當然是對 的正確態度
TRUSTWORTHY，是的，這是可能的。那麼為什麼在通過服務代理時它在 msdb 中作為訪客執行呢？
我最初認為這個問題是使用模擬時跨數據庫問題的典型原因：預設情況下，模擬數據庫級主體（這是
EXECUTE AS子句而不是語句可以做的所有事情）將被隔離到本地數據庫.然而，額外的測試和與 OP 的討論導致發現這種情況略有不同。似乎使用 Service Broker~~是模組簽名不能解決所有安全問題的極少數情況之一。~~這就是我在設置模組簽名的典型實現時所想到的，因為它不起作用。所以，我嘗試了幾件事，發現只有 SQLCLR 能夠做到這一點。
然後最近我發現了一個相關問題，sa doesn’t have permission to other database through synonyms with the Service Broker，引用了@Remus Rusanu的一篇文章，Remus 說這確實是可能的。確定 Remsus 的範常式式碼有效，我得出結論，我一定遺漏了一些小細節。而且，在查看細節時，我發現使用了一個違反直覺的選項：
> > 更改過程以具有 EXECUTE AS 子句（否則程式碼簽名基礎結構不起作用） > > >
通常模組簽名允許您刪除
EXECUTE AS子句和語句，但在這裡它是必需的。由於 Service Broker 通過EXECUTE AS USER =語句在僅限數據庫的安全上下文中工作，因此需要它。通過將EXECUTE AS子句添加到CREATE PROCEDURE語句中，創建了一個新的安全上下文，它可以訪問伺服器級和/或其他數據庫，這是模組簽名設置的其餘部分處理的內容。太棒了，嗯，很抱歉把它弄對了，然後把它改成可行但不理想的東西，因為缺少那一塊;-)。但是，我現在讓它按照我最初說它應該工作的方式工作:-)。下面的第一組範常式式碼是純 T-SQL，可以使用的模組簽名方法
TRUSTWORTHY OFF（現在我添加了缺失的WITH EXECUTE AS N'dbo'）。我將把 SQLCLR 方法放在最底層，因為它確實有效，並且可能更適合其他一些場景。我希望通過簽署程序……我可以獲得跨數據庫傳輸的權限
你可以。我認為我從未見過您的模組簽名設置，但很可能是您錯過了我最初錯過的一個小的、非典型的選項（這是讓整個事情正常工作的關鍵）。
是否
execute as否定通常通過證書使用者關聯的任何權限？僅當它是
EXECUTE AS USER語句（不是語句的EXECUTE AS子句CREATE object，也不是EXECUTE AS LOGIN語句）時。在那種情況下，安全上下文是並且只能是僅數據庫，並且無法看到伺服器級或其他數據庫，即使模組簽名到位。而且，幸運的是，這（即EXECUTE AS USER語句）正是 Service Broker 為執行啟動過程所做的事情。所以，是的，這就是阻止您最初嘗試進行模組簽名的原因。並且，修復它的技巧是簡單地將一個WITH EXECUTE AS N'dbo'子句添加到CREATE PROCEDURE訪問另一個數據庫的 proc 的語句中。您使用什麼使用者並不重要，但我發現dbo簡化為使用OWNER如果所有者發生更改，則警告需要重新簽署儲存過程。當然，也可以更改數據庫的所有者，所以我本來希望我的選擇也會收到警告，但事實並非如此，所以我暫時選擇忽略這種潛在的細微差別;-)。理想解決方案 (T-SQL)
主要設置
USE [master]; GO IF (DB_ID(N'SendDbMailFromServiceBrokerQueue') IS NOT NULL) BEGIN RAISERROR(N'Dropping DB: [SendDbMailFromServiceBrokerQueue]...', 10, 1) WITH NOWAIT; ALTER DATABASE [SendDbMailFromServiceBrokerQueue] SET OFFLINE WITH ROLLBACK IMMEDIATE; ALTER DATABASE [SendDbMailFromServiceBrokerQueue] SET ONLINE WITH ROLLBACK IMMEDIATE; DROP DATABASE [SendDbMailFromServiceBrokerQueue]; END RAISERROR(N'Creating DB: [SendDbMailFromServiceBrokerQueue]...', 10, 1) WITH NOWAIT; CREATE DATABASE [SendDbMailFromServiceBrokerQueue] COLLATE Latin1_General_100_CI_AS_KS_SC WITH DB_CHAINING OFF, TRUSTWORTHY OFF; ALTER DATABASE [SendDbMailFromServiceBrokerQueue] SET RECOVERY SIMPLE, PAGE_VERIFY CHECKSUM, ENABLE_BROKER; GO ------------------------------------------------- USE [SendDbMailFromServiceBrokerQueue]; GO CREATE SCHEMA [FunStuff] AUTHORIZATION [dbo]; GO CREATE USER [BrokerUser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]; CREATE QUEUE [FunStuff].[SendingQueue]; CREATE SERVICE [//SendingService] AUTHORIZATION [dbo] ON QUEUE [FunStuff].[SendingQueue]; CREATE MESSAGE TYPE [//AuditMessage] AUTHORIZATION [dbo] VALIDATION = NONE; CREATE CONTRACT [//AuditContract] AUTHORIZATION [dbo] ([//AuditMessage] SENT BY INITIATOR); GO CREATE PROCEDURE [FunStuff].[SendMessage] ( @Content NVARCHAR(MAX) ) AS SET NOCOUNT ON; DECLARE @ConversationHandle UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @ConversationHandle FROM SERVICE [//SendingService] TO SERVICE '//ReceivingService' ON CONTRACT [//AuditContract] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE [//AuditMessage] (@Content) ; END CONVERSATION @ConversationHandle ; GO --------------------------------------------------------------------------- GO CREATE PROCEDURE [dbo].[EmailHandler] ( @EmailSubject VARCHAR(255), @EmailContent NVARCHAR(MAX) ) WITH EXECUTE AS N'dbo' -- THIS IS REQUIRED (when used with Service Broker)!!! AS DECLARE @Recipients NVARCHAR(4000) = N'recipient@place.tld'; EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = N'{my_pofile_name}', @recipients = @Recipients, @subject = @EmailSubject, @body = @EmailContent, @exclude_query_output = 1; GO CREATE PROCEDURE [FunStuff].[AuditMessageHandler] ( @EmailSubject VARCHAR(255), @EmailContent NVARCHAR(MAX) ) AS EXECUTE [dbo].[EmailHandler] @EmailSubject, @EmailContent; GO CREATE PROCEDURE [FunStuff].[AuditActivation] AS SET XACT_ABORT ON; DECLARE @ConversationHandle UNIQUEIDENTIFIER, @ConversationGroupID UNIQUEIDENTIFIER, @MessageBody NVARCHAR(MAX), @MessageTypeName NVARCHAR(256), @RowsReceived INT; WHILE (1 = 1) BEGIN WAITFOR( GET CONVERSATION GROUP @ConversationGroupID FROM [FunStuff].[ReceivingQueue] ), TIMEOUT 500; IF (@ConversationGroupID IS NULL) BEGIN BREAK; END; WHILE (2 = 2) BEGIN BEGIN TRANSACTION; RECEIVE TOP (1) @ConversationHandle = [conversation_handle], @MessageTypeName = [message_type_name], @MessageBody = [message_body] FROM [FunStuff].[ReceivingQueue] WHERE CONVERSATION_GROUP_ID = @ConversationGroupID; SET @RowsReceived = @@ROWCOUNT; IF (@RowsReceived = 0) BEGIN COMMIT; BREAK; END; IF (@MessageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog') BEGIN END CONVERSATION @ConversationHandle; END; IF (@MessageTypeName = N'//AuditMessage') BEGIN EXEC [FunStuff].[AuditMessageHandler] N'Email From Broker test', @MessageBody; END; COMMIT TRANSACTION; END; -- WHILE (2 = 2) END; -- WHILE (1 = 1) GO GRANT EXECUTE ON [FunStuff].[AuditActivation] TO [BrokerUser]; GO CREATE QUEUE [FunStuff].[ReceivingQueue] WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = [FunStuff].[AuditActivation], MAX_QUEUE_READERS = 1, EXECUTE AS N'BrokerUser' ); CREATE SERVICE [//ReceivingService] AUTHORIZATION [dbo] ON QUEUE [FunStuff].[ReceivingQueue] ([//AuditContract]); GO完成這項工作的模組簽名步驟
USE [SendDbMailFromServiceBrokerQueue]; CREATE CERTIFICATE [Permission:SendDbMail] ENCRYPTION BY PASSWORD = N'MyCertificate!MineMineMine!' WITH SUBJECT = N'Grant permission to Send DB Mail', EXPIRY_DATE = '2099-12-31'; -- Sign the Stored Procedure that accesses another DB ADD SIGNATURE TO [dbo].[EmailHandler] BY CERTIFICATE [Permission:SendDbMail] WITH PASSWORD = N'MyCertificate!MineMineMine!'; -- Copy the Certificate to [msdb] DECLARE @PublicKey VARBINARY(MAX), @SQL NVARCHAR(MAX); SET @PublicKey = CERTENCODED(CERT_ID(N'Permission:SendDbMail')); SET @SQL = N' CREATE CERTIFICATE [Permission:SendDbMail] FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @PublicKey, 1) + N';'; PRINT @SQL; -- DEBUG EXEC [msdb].[sys].[sp_executesql] @SQL; -- Create the Certificate-based User in [msdb] EXEC [msdb].[sys].[sp_executesql] N'CREATE USER [Permission:SendDbMail] FROM CERTIFICATE [Permission:SendDbMail]; GRANT AUTHENTICATE TO [Permission:SendDbMail]; PRINT ''Adding Certificate-based User to DB Role [DatabaseMailUserRole]...''; EXEC sp_addrolemember N''DatabaseMailUserRole'', N''Permission:SendDbMail''; ';測試
USE [SendDbMailFromServiceBrokerQueue]; -- execute statement below if there is an error and the queue is disabled: -- ALTER QUEUE [FunStuff].[ReceivingQueue] WITH STATUS = ON, ACTIVATION (STATUS = ON); EXEC [FunStuff].[SendMessage] @Content = N'Woo hoo!';替代解決方案 (SQLCLR)
我還能夠使用 SQLCLR 來實現它（是的，沒有啟用
TRUSTWORTHY:-)。SQLCLR C# 程式碼
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public class UserDefinedFunctions { [SqlProcedure()] public static void ExecSendDbMail([SqlFacet(MaxSize = 255)] SqlString EmailSubject, SqlString EmailContent) { using (SqlConnection _Connection = new SqlConnection("Server=(local); Trusted_Connection=true; Enlist=false;")) { using (SqlCommand _Command = _Connection.CreateCommand()) { _Command.CommandType = CommandType.StoredProcedure; _Command.CommandText = @"dbo.sp_send_dbmail"; SqlParameter _ParamProfileName = new SqlParameter("profile_name", SqlDbType.NVarChar, 128); _ParamProfileName.Value = "{replace_with_your_profile_name}"; _Command.Parameters.Add(_ParamProfileName); SqlParameter _ParamRecipients = new SqlParameter("recipients", SqlDbType.VarChar, (int)SqlMetaData.Max); _ParamRecipients.Value = "{replace_with_your_recipients}"; _Command.Parameters.Add(_ParamRecipients); SqlParameter _ParamSubject = new SqlParameter("subject", SqlDbType.NVarChar, 255); _ParamSubject.Value = EmailSubject.Value; _Command.Parameters.Add(_ParamSubject); SqlParameter _ParamBody = new SqlParameter("body", SqlDbType.NVarChar, (int)SqlMetaData.Max); _ParamBody.Value = EmailContent.Value; _Command.Parameters.Add(_ParamBody); SqlParameter _ParamExcludeQueryOutput = new SqlParameter("exclude_query_output", SqlDbType.Bit); _ParamExcludeQueryOutput.Value = true; _Command.Parameters.Add(_ParamExcludeQueryOutput); _Connection.Open(); _Connection.ChangeDatabase("msdb"); _Command.ExecuteNonQuery(); } } return; } }設置
USE [master]; CREATE DATABASE [SendDbMailFromServiceBrokerQueue] COLLATE Latin1_General_100_CI_AS_SC WITH DB_CHAINING OFF, TRUSTWORTHY OFF; ALTER DATABASE [SendDbMailFromServiceBrokerQueue] SET RECOVERY SIMPLE, PAGE_VERIFY CHECKSUM, ENABLE_BROKER; GO -- Create objects needed to allow for EXTERNAL_ACCESS without TRUSTWORTHY ON: CREATE ASYMMETRIC KEY [Permission:SendDbMail$Key] FROM EXECUTABLE FILE = N'C:\...\NoTrustworthy.dll'; CREATE LOGIN [Permission:SendDbMail$Login] FROM ASYMMETRIC KEY [Permission:SendDbMail$Key]; GRANT EXTERNAL ACCESS ASSEMBLY TO [Permission:SendDbMail$Login]; GO ------------------------------------------------- USE [SendDbMailFromServiceBrokerQueue]; GO CREATE ASSEMBLY [NoTrustworthy] AUTHORIZATION [dbo] FROM N'C:\...\NoTrustworthy.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS; GO CREATE PROCEDURE [dbo].[ExecSendDbMail] ( @EmailSubject NVARCHAR (255), @EmailContent NVARCHAR (MAX) ) AS EXTERNAL NAME [NoTrustworthy].[UserDefinedFunctions].[ExecSendDbMail]; GO CREATE SCHEMA [FunStuff] AUTHORIZATION [dbo]; GO CREATE USER [BrokerUser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]; CREATE QUEUE [FunStuff].[SendingQueue]; CREATE SERVICE [//SendingService] AUTHORIZATION [dbo] ON QUEUE [FunStuff].[SendingQueue]; CREATE MESSAGE TYPE [//AuditMessage] AUTHORIZATION [dbo] VALIDATION = NONE; CREATE CONTRACT [//AuditContract] AUTHORIZATION [dbo] ([//AuditMessage] SENT BY INITIATOR); GO CREATE PROCEDURE [FunStuff].[SendMessage] ( @Content NVARCHAR(MAX) ) AS SET NOCOUNT ON; DECLARE @ConversationHandle UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @ConversationHandle FROM SERVICE [//SendingService] TO SERVICE '//ReceivingService' ON CONTRACT [//AuditContract] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE [//AuditMessage] (@Content) ; END CONVERSATION @ConversationHandle ; GO --------------------------------------------------------------------------- CREATE PROCEDURE [dbo].[EmailHandler] ( @EmailSubject VARCHAR(255), @EmailContent NVARCHAR(MAX) ) AS -- other logic EXEC [dbo].[ExecSendDbMail] @EmailSubject, @EmailContent; GO GO CREATE PROCEDURE [FunStuff].[AuditMessageHandler] ( @EmailSubject VARCHAR(255), @EmailContent NVARCHAR(MAX) ) AS EXECUTE [dbo].[EmailHandler] @EmailSubject, @EmailContent; GO CREATE PROCEDURE [FunStuff].[AuditActivation] AS SET XACT_ABORT ON; DECLARE @ConversationHandle UNIQUEIDENTIFIER, @ConversationGroupID UNIQUEIDENTIFIER, @MessageBody NVARCHAR(MAX), @MessageTypeName NVARCHAR(256), @RowsReceived INT; WHILE (1 = 1) BEGIN WAITFOR( GET CONVERSATION GROUP @ConversationGroupID FROM [FunStuff].[ReceivingQueue] ), TIMEOUT 500; IF (@ConversationGroupID IS NULL) BEGIN BREAK; END; WHILE (2 = 2) BEGIN BEGIN TRANSACTION; PRINT 'Get Message'; RECEIVE TOP (1) @ConversationHandle = [conversation_handle], @MessageTypeName = [message_type_name], @MessageBody = [message_body] FROM [FunStuff].[ReceivingQueue] WHERE CONVERSATION_GROUP_ID = @ConversationGroupID; SET @RowsReceived = @@ROWCOUNT; IF (@RowsReceived = 0) BEGIN COMMIT; BREAK; END; IF (@MessageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog') BEGIN END CONVERSATION @ConversationHandle; END; IF (@MessageTypeName = N'//AuditMessage') BEGIN EXEC [FunStuff].[AuditMessageHandler] N'Email From Broker test', @MessageBody; END; COMMIT TRANSACTION; END; -- WHILE (2 = 2) END; -- WHILE (1 = 1) GO GRANT EXECUTE ON [FunStuff].[AuditActivation] TO [BrokerUser]; GO CREATE QUEUE [FunStuff].[ReceivingQueue] WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = [FunStuff].[AuditActivation], MAX_QUEUE_READERS = 1, EXECUTE AS N'BrokerUser' ); CREATE SERVICE [//ReceivingService] AUTHORIZATION [dbo] ON QUEUE [FunStuff].[ReceivingQueue] ([//AuditContract]); GO ---------------------------------------------------------------------------測試
USE [SendDbMailFromServiceBrokerQueue]; -- execute statement below if there is an error and the queue is disabled: -- ALTER QUEUE [FunStuff].[ReceivingQueue] WITH STATUS = ON, ACTIVATION (STATUS = ON); EXEC [FunStuff].[SendMessage] @Content = N'try me!';清理
IF (DB_ID(N'SendDbMailFromServiceBrokerQueue') IS NOT NULL) BEGIN RAISERROR(N'Dropping DB: [SendDbMailFromServiceBrokerQueue]...', 10, 1) WITH NOWAIT; ALTER DATABASE [SendDbMailFromServiceBrokerQueue] SET OFFLINE WITH ROLLBACK IMMEDIATE; ALTER DATABASE [SendDbMailFromServiceBrokerQueue] SET ONLINE WITH ROLLBACK IMMEDIATE; DROP DATABASE [SendDbMailFromServiceBrokerQueue]; END DROP LOGIN [Permission:SendDbMail$Login]; DROP ASYMMETRIC KEY [Permission:SendDbMail$Key];
我在這裡發布我對這個問題的發現。我的第一個答案是這個：
- 在其中創建證書
master並從中創建登錄名；- 將此登錄映射到
msdb並包含相應的使用者DatabaseMailUserRole- 將證書導出到您的使用者數據庫
- 使用此證書籤署您的啟動過程
甚至在我嘗試之前，Solomon Rutzky 就給了我這個答案的連結，我也懷疑我只會拒絕
DatabaseMailUserRole會員資格，所以它發生了：
上圖顯示了我的過程以及使用者測試如何執行它。重要的是要了解該過程（特別是啟動過程）將始終作為使用者執行，而不是登錄，這是設計使然：內部啟動上下文
為啟動配置的隊列還必須指定啟動儲存過程作為 執行的使用者。SQL Server在啟動儲存過程之前模擬此使用者。
所以我通過沒有登錄的使用者測試來執行它，我是這樣創建的：
create user test without login好的，我看到了預期的結果：我簽署的 proc 已獲得會員資格，
DatabaseMailUserRole但具有DENY ONLY.現在我可以回到 OP 的問題：
即使服務代理在 TheUser 的登錄下執行，由於某種原因，它也在 guest 的數據庫使用者下執行，我懷疑這至少部分解釋了我的權限問題。
登錄名 TheUser 也映射到 msdb 中名為 TheUser 的使用者 - 它當然沒有映射到 guest。
那麼為什麼在通過服務代理時它在 msdb 中作為訪客執行呢？
錯誤在這裡：啟動過程未在login下執行。它總是在user下執行。
這就是為什麼我什至不需要設置服務代理、隊列、啟動過程……使用使用者執行的簡單過程進行測試就足夠了
現在是最後的部分。為了使我的解決方案有效，我需要最後一個條件為真：使用者數據庫必須
TRUSTWORTHY與擁有AUTHENTICATE SERVER權限的所有者一起使用。這樣，證書使用者將能夠退出他的沙箱數據庫，它不會是guestin，msdb但它將是證書使用者：
我認為這不是一個好的解決方案，我一直反對製作 user database
TRUSTWORTHY。僅當該使用者數據庫沒有同時存在時
db_owner才是安全的sysadminPS 我贊成Solomon Rutzky完全不接觸
TRUSTWORTHY的解決方案。
