sql-server - Service Broker 中的存储过程无法访问同一服务器实例下的另一个数据库

Question

我创建了一个服务代理，它通过在表上的触发器的帮助下形成的队列执行。我必须从我当前数据库的 Queue_Procedure 中调用另一个数据库的存储过程/函数。当尝试执行它时，我遇到了错误。

在队列“FirstDatabase.dbo.TestQueue_Test_RJ”上运行的已激活过程“[dbo].[spGenerateProc_Test_RJ]”输出以下内容：“服务器主体“sa”在当前安全上下文下无法访问数据库“SecondDatabase”。

以下是我用来创建服务代理队列并使用它的步骤中的所有查询。

- 步骤1：

Use FirstDatabaseName;
ALTER DATABASE FirstDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

- 第2步：

CREATE TABLE PriceData_auditlog_Test_RJ
(
            xmlstring xml
)
GO

CREATE PROCEDURE [dbo].[spGenerateProc_Test_RJ]
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @message_type varchar(100)
    DECLARE @dialog uniqueidentifier, @message_body XML;
    WHILE (1 = 1)
    BEGIN -- Receive the next available message from the queue
        WAITFOR (
            RECEIVE TOP(1) @message_type = message_type_name,
            @message_body = CAST(message_body AS XML),
            @dialog = conversation_handle
            FROM dbo.TestQueue_Test_RJ
        ), TIMEOUT 500
        IF (@@ROWCOUNT = 0 OR @message_body IS NULL)
        BEGIN
            BREAK
        END
        ELSE
        BEGIN
            --process xml message here...
            INSERT INTO PriceData_auditlog_Test_RJ values(@message_body)

            SELECT BlobData.dbo.Get_BlobTagsCount_Test_RJ()
        END

        END CONVERSATION @dialog
    END
END

--step 3：创建消息类型

CREATE MESSAGE TYPE TestMessage_Test_RJ
AUTHORIZATION dbo
VALIDATION = WELL_FORMED_XML;

--第四步：创建合约

CREATE CONTRACT TestContract_Test_RJ
AUTHORIZATION dbo
(TestMessage_Test_RJ SENT BY INITIATOR);

-- 第五步：创建队列

CREATE QUEUE dbo.TestQueue_Test_RJ WITH STATUS=ON, ACTIVATION
(STATUS = ON, MAX_QUEUE_READERS = 7,
PROCEDURE_NAME = [dbo].[spGenerateProc_Test_RJ],   EXECUTE AS N'dbo');

-- 第六步：创建服务发起者

CREATE SERVICE TestServiceInitiator_Test_RJ
AUTHORIZATION dbo
ON QUEUE dbo.TestQueue_Test_RJ (TestContract_Test_RJ);

--step 7：创建目标服务

CREATE SERVICE [TestServiceTarget_Test_RJ]
AUTHORIZATION dbo
ON QUEUE dbo.TestQueue_Test_RJ (TestContract_Test_RJ);

--步骤 8：在 FirstDatabase 表上创建触发器

CREATE  TRIGGER [dbo].[Trg_PriceData_Update_Test_RJ]
    ON  FirstDatabaseName.dbo.Price
    FOR UPDATE
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @MessageBody XML

        --get relevant information from inserted/deleted and convert to xml message
        SET @MessageBody = (SELECT ProductID FROM inserted FOR XML AUTO)

        If (@MessageBody IS NOT NULL)
        BEGIN

            SELECT @MessageBody

            DECLARE @Handle UNIQUEIDENTIFIER;

            BEGIN DIALOG CONVERSATION @Handle
                FROM SERVICE [TestServiceInitiator_Test_RJ]
                TO SERVICE 'TestServiceTarget_Test_RJ'
                ON CONTRACT [TestContract_Test_RJ]
                WITH ENCRYPTION = OFF;

            SEND ON CONVERSATION @Handle
            MESSAGE TYPE [TestMessage_Test_RJ](@MessageBody);

        END
    END
    GO

--更新语句以触发执行服务代理队列的触发器

Update FirstDatabaseName.dbo.Price
SET Price=141.833
WHERE ID=1408166

我也遵循了博客链接中提到的步骤

但我仍然遇到同样的错误。

The activated proc '[dbo].[spGenerateProc_Test_RJ]' running on queue 'FirstDatabase.dbo.BundleQueue_Test_RJ' output the following: 'The server principal "sa" is not able to access the database "SecondDatabase" under the current security context.'

编辑：通过引用链接，以下是我用于创建证书以在其他数据库中使用函数/过程的步骤。

源数据库配置

--Certificate Installation Start
-- Create a certificate with a private key

USE [FirstDatabseName]
GO
CREATE CERTIFICATE [spGenerateBundleProcAudit_Test_RJ]
      ENCRYPTION BY PASSWORD = 'Password#1234'
      WITH SUBJECT = 'spGenerateBundleProcedure Signing  for audit certificate';
GO

--Sign the procedure with the certificate’s private key
ADD SIGNATURE TO OBJECT::[spGenerateBundleProc_Test_RJ]
      BY CERTIFICATE [spGenerateBundleProcAudit_Test_RJ]
            WITH PASSWORD = 'Password#1234';

GO

--Drop the private key. This way it cannot be
-- used again to sign other procedures.
ALTER CERTIFICATE [spGenerateBundleProcAudit_Test_RJ]
      REMOVE PRIVATE KEY;
GO

--We must backup to a file and create
--the certificate in [master] from this file

BACKUP CERTIFICATE [spGenerateBundleProcAudit_Test_RJ]
      TO FILE = 'C:\spGenerateBundleProcAudit2_Test_RJ.CER';
GO

目标数据库配置

USE [SecondDatabseName]
GO

--DROP CERTIFICATE [spGenerateBundleProcAudit_Test_RJ]
CREATE CERTIFICATE [spGenerateBundleProcAudit_Test_RJ]
      FROM FILE = 'C:\spGenerateBundleProcAudit2_Test_RJ.CER';
GO

--DROP USER [spGenerateBundleProcAudit_Test_RJ]
CREATE USER [spGenerateBundleProcAudit_Test_RJ]
    FROM CERTIFICATE [spGenerateBundleProcAudit_Test_RJ];
GO

--‘AUTHENTICATE’ permission is required for all other permissions to take effect
GRANT AUTHENTICATE TO [spGenerateBundleProcAudit_Test_RJ];

GRANT EXECUTE ON [Get_BlobTagsCount_Test_RJ] TO [spGenerateBundleProcAudit_Test_RJ];
GO

--Enable back the disabled ‘SessionsService’ queue
ALTER QUEUE dbo.TestQueue_Test_RJ WITH STATUS=ON
GO

--Certificate Installation End

标签： sql-servertsqlstored-proceduressql-server-2012service-broker