"service broker" The server principal "sa" is not able to access the database under the current security context

Question

I'm working with SQL Server 2012 Express.

I'm using Service Broker to run a stored procedure asynchronously.

The activation procedure has to access another database to execute another stored procedure. This is the code:

CREATE PROCEDURE [dbo].[GetNewCodes]
    @gintNewCodes bigint,   
    @presNewCodes tinyint,
    @levelNewCodes bigint,
    @quantityNewCodes smallint
AS

    -- Get new codes from INCIC database.
    DECLARE @return_value int,
            @xmlGenerated xml,
            @xmlString NVARCHAR(MAX)

    SET NOCOUNT ON;

        -- Set that this stored procedure is running
        update dbo.RunningSPs with (serializable) set conf_value = 1
        where sp_name = N'GetNewCodes'

        if @@rowcount = 0
        begin
            insert dbo.RunningSPs(sp_name, conf_value) values (N'GetNewCodes', 1)
        end

    EXEC    @return_value = [INCIC].[dbo].[ReadCodeBuffer]
            @gint = @gintNewCodes,
            @pres = @presNewCodes,
            @level = @levelNewCodes,
            @quantity = @quantityNewCodes,
            @xmlGenerated = @xmlGenerated OUTPUT

    SET @xmlString = cast(@xmlGenerated as nvarchar(max))

    -- Process these new codes on TRZ.
    EXEC dbo.ProcessCodes @XmlString = @xmlString

    -- Update that we are not running this procedure any more.
    update dbo.RunningSPs with (serializable) set conf_value = 0
    where sp_name = N'GetNewCodes'

    if @@rowcount = 0
    begin
        insert dbo.RunningSPs(sp_name, conf_value) values (N'GetNewCodes', 0)
    end

The problem is here: [INCIC].[dbo].[ReadCodeBuffer], and the error message is:

Error: 50000
Unrecoverable error in procedure GetNewCodes: 916: The server principal "sa" is not able to access the database "INCIC" under the current security context.

I have followed this tutorial to implement Service, queue and activation stored procedure.

How can I fix this problem?

score 5 · Accepted Answer · answered Apr 12 '14 at 07:13

Read Call a procedure in another database from an activated procedure.

The problem is that activated procedures are run under an EXECUTE AS USER context and as such are subject to database impersonation restrictions (they are sandboxed within the database). Is all explained in Extending Database Impersonation by Using EXECUTE AS.

The solution is to sign your activated procedure and create an user derived from the signing certificate in the target database, and grant this derived user the required permissions. The first link shows a full example.

score 0 · Answer 2 · answered May 05 '16 at 16:40

Remus Rusanu's answer is clearly definitive and correct. (Anyone dealing with Broker Services knows his expertise and invaluable blog.)

I just wanted to document my experience, since google will direct to this question when searching on "The server principal "sa" is not able to access the database..."

In my case, I was call another database from within an activated procedure, but invoking a sql statement directly, not a stored procedure.

Originally, the cross-database call worked fine without a signed certificate and the use of impersonation. Then, after a small syntax change, it started returning the above error message.

Here is what worked without the need for a signed certificate:

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyOtherDb')

and here is what provoked the security exception:

IF DB_ID(N'MyOtherDb') IS NOT NULL

"service broker" The server principal "sa" is not able to access the database under the current security context

2 Answers2