Permissions in triggers when using cross database certificates

12 votes
1 answer
3222 views
sql-server permissions trigger certificate signature
                          I use cross database certificates (as explained by Erland Sommarskog ) to control access to a certain database in my environment ( SQL Server 2008 R2 ).  

I have stored procedures in database A that update tables in database B.  This has always worked for a variety of stored procedures in db A and tables in db B until now.  I am trying to update a table in db B, but the table has a trigger on it.  This trigger is inserting additional data in another table in db B.  I am getting the error:

> Msg 916, Level 14, State 1, Procedure table_trigger, Line 11 The server
> principal "sql\login" is not able to access the database
> "B" under the current security context.

I tried granting insert permissions for database B user that is tied to the certificate to insert into that other table, but it did not resolve the error.  Do I have any options other than changing the trigger so it uses WITH EXECUTE AS OWNER?

Here is DDL to replicate the issue:

    CREATE LOGIN [GuggTest] WITH PASSWORD=N'abcd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    
    CREATE DATABASE A;
    CREATE DATABASE B;
    
    USE A;
    
    CREATE TABLE dbo.SPtoUpdate
        (
          ID INT
        , ILoveFishing VARCHAR(255)
        );
    INSERT INTO dbo.SPtoUpdate
            ( ID , ILoveFishing )
    VALUES  ( 1,'Musky'),( 2,'Pike'),( 3,'Yellow Perch');
    CREATE TABLE dbo.TriggerToInsert
        (
          ID INT
        , ILoveFishing VARCHAR(255)
        , ChangeDate DATETIME2
        );
    GO
    
    CREATE TRIGGER dbo.SPtoUpdateTrigger ON dbo.SPtoUpdate
        FOR UPDATE
    AS
        DECLARE @datetime DATETIME2;
        SELECT  @datetime = GETDATE()
    
        INSERT  INTO dbo.TriggerToInsert
                ( ID , ILoveFishing , ChangeDate )
        VALUES  ( 1 , 'Yes' , @datetime );
    GO
    
    CREATE CERTIFICATE BExecutor
       ENCRYPTION BY PASSWORD = 'Obfuscated'
       WITH SUBJECT = 'Execute sp from B to A',
       START_DATE = '20140101', EXPIRY_DATE = '20300101'
    GO
    
    BACKUP CERTIFICATE BExecutor TO FILE = 'C:\temp\crossdbcert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                      ENCRYPTION BY PASSWORD = 'Obfuscated',
                      DECRYPTION BY PASSWORD = 'Obfuscated')
    GO
    
    CREATE USER BExecutor FROM CERTIFICATE BExecutor
    
    GRANT UPDATE ON dbo.SPtoUpdate TO BExecutor
    GRANT SELECT ON dbo.SPtoUpdate TO BExecutor
    --Also give insert on dbo.TriggerToInsert
    GRANT INSERT ON dbo.TriggerToInsert TO BExecutor
    
    USE B
    GO
    
    CREATE USER [GuggTest] FOR LOGIN [GuggTest];
    EXEC sp_addrolemember N'db_owner', N'GuggTest'
    GO
    
    CREATE PROCEDURE dbo.UpdateTableInA
    AS
        BEGIN
            UPDATE  A.dbo.SPtoUpdate
            SET     ILoveFishing = 'Walleye'
            WHERE   ID = 2;
        END
    
    GO
    
    
    CREATE CERTIFICATE BExecutor FROM FILE = 'C:\temp\crossdbcert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                      ENCRYPTION BY PASSWORD = 'Obfuscated',
                      DECRYPTION BY PASSWORD = 'Obfuscated')
    GO
    
    EXEC master..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output'
    GO
    
    ADD SIGNATURE TO dbo.UpdateTableInA BY CERTIFICATE BExecutor
        WITH PASSWORD = 'Obfuscated'
    GO
    
    --Log In or Change execution context to GuggTest, then EXEC dbo.UpdateTableInA
                        
Asked by Dave.Gugg (331 rep)
Jan 13, 2017, 10:39 PM
Last activity: Mar 6, 2017, 11:19 PM
Permissions in triggers when using cross database certificates

Related Questions
