The weather has been awesome here for the last few days. Sixty plus degree temperatures has made it feel more like May than February. It isn’t supposed to last much longer, but I have enjoyed it. I took the boat in for an engine tune-up this weekend, which means I should get it back just in time for most the ice to be coming off the lakes. I’m hoping to take a couple more shots at the Wolf River walleye run this spring. Last year didn’t provide good results.
I took my sons to a park on the edge of a lake this past weekend and happened to be watching while an unfortunate ice fisherman’s ATV fell through the ice. I’m not sure how these ice fishermen know what ice is good versus what ice is bad, but you can see from the main picture above that not all of them know either. Fortunately, only the front tires went through and another ATV came over and pulled him out.
I ran into an issue with cross database certificates recently. I have blogged about how to set these certificates up here – they are a handy way to enable permissions across databases. However, I ran into a problem where the permission chain failed due to a trigger on the original table that updated a separate table. Here is the SQL 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
It turns out you can counter sign a trigger with the certificate, and this will allow the permission chain to succeed. By doing this, you don’t even need to grant the certificate user permission to the second table. Here is the syntax to do that:
ADD COUNTER SIGNATURE TO dbo.SPtoUpdateTrigger BY CERTIFICATE BExecutor WITH PASSWORD = 'Obfuscated';
Use this technique to work with cross database permissions that have to access tables with triggers.