跨库DSQL操作致SQL Server Service Broker故障排查

问题现象 #

• 基于Eitan Blumin的示例搭建Service Broker并编写自定义存储过程，同库操作可正常执行，但跨库操作时触发异常
• 手动执行存储过程无问题，但Service Broker异步执行时会报错

同库正常执行代码 #

DELETE FROM CurrentDB.dbo.[gis_pt_im_map_auth_label] 
WHERE plotnumber IN (' + @ids + ')

跨库执行失败代码 #

DELETE FROM OtherDB.OtherSchema.[gis_pt_im_map_auth_label] 
WHERE plotnumber IN (' + @ids + ')

涉及的存储过程代码 #

CREATE PROCEDURE [dbo].[sp_Location_UPDATE_TEST3] 
    @inserted XML,
    @deleted  XML = NULL
AS
    SET NOCOUNT ON;

    BEGIN TRY
    DECLARE @SQL NVARCHAR(max)
    DECLARE @ids NVARCHAR(max)

    IF EXISTS (SELECT NULL FROM @inserted.nodes('inserted/row') AS T(X))
    BEGIN
        INSERT INTO [dbo].[LocationViewLog] ([lo_location], [lo_Location_Code])
            SELECT 
                inserted.[lo_location],
                inserted.lo_location_Code            
            FROM
                (SELECT
                     --X.query('.').value('(row/PurchaseOrderID)[1]', 'int') AS PurchaseOrderID
                     X.query('.').value('(row/lo_Location)[1]', 'int') AS lo_location,
                     X.query('.').value('(row/lo_Location_Code)[1]', 'nvarchar(100)') AS lo_location_Code
                 FROM 
                     @inserted.nodes('inserted/row') AS T(X)) AS inserted 

           SELECT @ids = String_agg(inserted.lo_location, ',')
                    FROM 
                    (SELECT
                         X.query('.').value('(row/lo_Location)[1]', 'int') AS lo_location
                    FROM @inserted.nodes('inserted/row') AS T(X)
                     ) AS inserted 

--问题代码块：跨库执行DELETE时Service Broker报错，同库则正常
            SET @SQL = 'DELETE FROM Test.dbo.[gis_pt_im_map_auth_label] WHERE  plotnumber IN ('+@ids+')';
                       INSERT INTO [dbo].[LocationViewLog]
                   (lo_Location_Code) VALUES (@SQL);
            EXEC(@SQL) 
--问题代码块结束
        END
    END TRY
    BEGIN CATCH
        -- 由于是异步触发器，回滚更新操作比常规触发器复杂得多
        -- 目前针对该场景，我们承担部分数据不一致的风险

        EXECUTE [dbo].[uspLogError];
    END CATCH;

排查与解决 #

• 最初怀疑是用户权限问题，尝试将执行用户设为两个数据库的dbo，但未解决
• 最终通过模块签名解决问题，核心是通过证书为存储过程授权，让其获得跨库操作的权限，而非依赖执行用户本身的权限，具体实现步骤如下：

模块签名核心实现步骤 #

1. 在当前数据库创建证书并为存储过程签名

-- 创建证书
CREATE CERTIFICATE Cert_SP_CrossDB
WITH SUBJECT = '用于跨库存储过程权限的证书';

-- 为目标存储过程签名
ADD SIGNATURE TO [dbo].[sp_Location_UPDATE_TEST3]
BY CERTIFICATE Cert_SP_CrossDB;

2. 导出证书并导入到目标数据库

-- 将证书导出到本地文件（需替换为实际可访问路径）
BACKUP CERTIFICATE Cert_SP_CrossDB
TO FILE = 'C:\Temp\Cert_SP_CrossDB.cer';

-- 切换到目标数据库并导入证书
USE OtherDB;
CREATE CERTIFICATE Cert_SP_CrossDB
FROM FILE = 'C:\Temp\Cert_SP_CrossDB.cer';

3. 在目标数据库创建证书对应的用户并授权

USE OtherDB;
-- 创建关联证书的用户
CREATE USER User_SP_CrossDB
FOR CERTIFICATE Cert_SP_CrossDB;

-- 赋予该用户目标表的DELETE权限
GRANT DELETE ON OtherSchema.[gis_pt_im_map_auth_label] TO User_SP_CrossDB;

内容的提问来源于stack exchange，提问作者Andye