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
Best Answer
The problem here is that while the Certificate links the stored procedure in DatabaseA with the User in DatabaseB that has
INSERT
permissions on the two tables, the Trigger on the table being inserted into directly from the stored procedure is another module in the chain, and permissions acquired from Certificates do not pass along to other modules in a chain. Meaning, the Certificate allowed the Stored Procedure to insert into the table via the User, and even execute the Trigger. But, no permissions have been given to the Trigger to do anything related to objects (doing something likeSELECT 1;
would work).In this case, permissions need to be given to the Trigger, via that same Certificate, so that it can take whatever actions are necessary. This can be accomplished, at the very least, by counter-signing the Trigger. And you do that by executing
ADD COUNTER SIGNATURE TO [TriggerSchema].[TriggerName] BY CERTIFICATE ...;
. After that, it should just work, even without directINSERT
permission for the Certificate-based User on the table being inserted into by the Trigger.The example code below reproduces the problem, fixes the problem by adding a counter signature, but does not grant
INSERT
permissions to the trigger-populated table.CLEANUP
SETUP
TEST 1: Trigger fails
TEST 2: Trigger succeeds
Please note that the only change being made is
ADD COUNTER SIGNATURE
; there is noGRANT INSERT ON dbo.TablePopulatedByTrigger TO [PermissionsUser];
.