SQL Server Service Broker DSQL operation on another database causing Broker failure

Question

I have established a Service Broker configuration based on Eitan Blumin's excellent example.

https://eitanblumin.com/2018/10/31/advanced-service-broker-sample-asynchronous-triggers/

I have crafted my own stored procedure. I can get the example to work when I'm performing activities within the same database. If I try to operate outside the current database, I get errors.

I'm new to Service Brokers and I'm having trouble capturing the error but I suspect this is all part of the same problem.

This works

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

This fails

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

Below is the stored procedure in question. I suspect it is some kind of user permission but I have tried a couple of user based changes like making the executing user the DBO of both databases and no result.

For clarity if I execute the stored procedure manually, this will run. However Service Brokers changes the nature of the execution.

Any thoughts or inputs appreciated.

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 

--The following block is what is problematic.
--if I try to execute this delete in another database the Service Broker breaks as a result of an error.
--If I craft this same command to operate in the current database there's no problem
            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 Problem Block
        END
    END TRY
    BEGIN CATCH
        -- Since we're in an Asynchronous Trigger, rolling back an update operation
        -- is a lot more complicated than in a regular trigger.
        -- For now, for this scenario we'll take the risk of having partial data.

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

**EDIT. The answer provided by David Browne was indeed the issue. I found the follwo=ing two links to be exactly what I needed and demonstrated the implementation of Module Signing I needed. https://sqlundercover.com/2018/05/02/digitally-signing-a-stored-procedure-to-allow-it-to-run-with-elevated-permissions/

https://sqlundercover.com/2018/10/30/signing-stored-procedures-that-access-multiple-databases/

Answer 1

Service Broker Internal Activation runs your activation procedure using SQL Server impersonation. And SQL Server impersonation is limited to the current database.

A short explanation of the problem is that the activation execution context is trusted only in the database, not in the whole server. Anything related to the whole server, like a server level view or a dynamic management view or a linked server, acts as if you logged in as [Public].

The recommended way to solve the issue is to sign the procedure with a server level certificate that has the proper rights needed for the operation in question

Why does feature … not work under activation?

To sign the stored procedure, follow the instructions here: Tutorial: Signing Stored Procedures with a Certificate