none
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?
    Friday, April 11, 2014 9:19 AM

Answers

  • Hello,

    Did you check the database owner of the database"INCIC"?  If the database owner is not "SA" and  the Trustworthy propertity of the current database is OFF, you may receive this error when impersonating SA by using the EXECUTE AS in the stored procedure . Please refer to this article:

    when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

    To solve this issue, please change the database owner of "INCIC" to 'sa':

    USE INCIC 
    sp_changedbowner 'sa'

    Or turn on Trustworthy on the current database:

    ALTER DATABASE current_db SET TRUSTWORTHY ON

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here. 


    Fanny Liu
    TechNet Community Support


    Monday, April 14, 2014 8:27 AM
  • Setting TRUSTWORTHY ON is the quick and dirty solution. The safest way of doing it is by using code signing.

    The steps for are:

    • Alter your activation procedure to have EXECUTE AS OWNER
    • Create a certificate and sign the procedure
    • Export the certificate (only the public key) to a file.
    • Change database context to INCIC.
    • Create a certificate from the file
    • Create a user from the certificate
    • Grant AUTHENTICATE to the user
    • Grant EXEC on dbo.ReadCodeBuffer to the user

    Keep in mind that you have to sign the activation procedure again if you ALTER it.

    Remus Rusanu has an excellent article that describes step by step how to do it: http://rusanu.com/2006/03/07/call-a-procedure-in-another-database-from-an-activated-procedure/

    Tuesday, April 15, 2014 1:09 PM

All replies

  • Hello,

    Did you check the database owner of the database"INCIC"?  If the database owner is not "SA" and  the Trustworthy propertity of the current database is OFF, you may receive this error when impersonating SA by using the EXECUTE AS in the stored procedure . Please refer to this article:

    when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

    To solve this issue, please change the database owner of "INCIC" to 'sa':

    USE INCIC 
    sp_changedbowner 'sa'

    Or turn on Trustworthy on the current database:

    ALTER DATABASE current_db SET TRUSTWORTHY ON

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here. 


    Fanny Liu
    TechNet Community Support


    Monday, April 14, 2014 8:27 AM
  • Setting TRUSTWORTHY ON is the quick and dirty solution. The safest way of doing it is by using code signing.

    The steps for are:

    • Alter your activation procedure to have EXECUTE AS OWNER
    • Create a certificate and sign the procedure
    • Export the certificate (only the public key) to a file.
    • Change database context to INCIC.
    • Create a certificate from the file
    • Create a user from the certificate
    • Grant AUTHENTICATE to the user
    • Grant EXEC on dbo.ReadCodeBuffer to the user

    Keep in mind that you have to sign the activation procedure again if you ALTER it.

    Remus Rusanu has an excellent article that describes step by step how to do it: http://rusanu.com/2006/03/07/call-a-procedure-in-another-database-from-an-activated-procedure/

    Tuesday, April 15, 2014 1:09 PM