Execute a trigger always with sa user

Question

We have two databases on the same SQL Server instance. Both databases have different assigned SQL Server users. I want to execute a trigger in database 1, that should insert a record in database 2. Due to the fact, that in database 1 the trigger can be executed by a user, that has no access to database 2, the trigger could fail.

I'm looking for a way to execute the trigger in database 1 always in the context of the sa user.

Thanks in advance for any hint.

What you want is to write to the other database, not give sa privileges to random code. The simplest solution is to just give the proper permissions to the two accounts. Impersonating `sa` is not a solution at all — Panagiotis Kanavos, Feb 14 '21 at 21:32
You can use the [EXECUTE AS](https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-ver15) clause in a trigger, but *DON'T* execute as `sa`. At the very least, create a new account with a minimum of privileges. There's no point in refusing access to a user only to give full access to the trigger — Panagiotis Kanavos, Feb 14 '21 at 21:35
Perhaps see https://stackoverflow.com/questions/10031308/sql-server-2008-does-a-trigger-run-with-the-same-permissions-as-the-login-user but please keep security in mind as @PanagiotisKanavos has said — Charlieface, Feb 14 '21 at 21:36

score 2 · Answer 1 · answered Feb 14 '21 at 22:17

I suggest you sign the trigger with a certificate, copy the certificate to the other database, and grant permissions to a user based on the cert. That will allow cross-database access with the needed permissions even though the caller has no access to the other database. See this article for details.

Below is an example:

--Create self-signed cert in Database1 with auto-generated public/private key pair and copy to Database2
USE Database1;
CREATE CERTIFICATE your_certificate
    ENCRYPTION BY PASSWORD = 'password protecting certificate private key'
    WITH SUBJECT = 'For cross-database access';
GO

--Copy DBMK encrypted cert to another database.
--Cert private key is decrypted using third CERTPRIVATEKEY argument (decryption_password).
--Cert private key encrypted using the second CERTPRIVATEKEY argument (encryption password) to protect private key in transit.
DECLARE @cert_id int = CERT_ID('your_certificate');
DECLARE @public_key  varbinary(8000) = CERTENCODED(@cert_id),
        @private_key varbinary(8000) = CERTPRIVATEKEY(@cert_id , 'password protecting certificate private key during copy', 'password protecting certificate private key');

--these values should not be NULL
IF @cert_id IS NULL RAISERROR('Assertion failed: @cert_id is NULL',16,1);
IF @public_key IS NULL RAISERROR('Assertion failed: @public_key is NULL',16,1);
IF @private_key IS NULL RAISERROR('Assertion failed: @private_key is NULL',16,1);

--create same certificate in target database with cert private key encrypted by the target database DBMK
DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE your_certificate
       FROM  BINARY = ' + CONVERT(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          CONVERT(varchar(MAX), @private_key, 1)
        + ', DECRYPTION BY PASSWORD = ''password protecting certificate private key during copy'''
        + ', ENCRYPTION BY PASSWORD = ''password protecting certificate private key'');'
EXEC Database2.sys.sp_executesql @sql;
GO

--create certificate user in Database2 and grant permissions needed by the trigger
USE Database2;
CREATE USER your_certificate_user FROM CERTIFICATE your_certificate;
GRANT INSERT ON dbo.Table2 TO your_certificate_user;
GO

--add signature to trigger by the same certificate that exists in both databases
USE Database1;
ADD SIGNATURE TO dbo.TR_table1 
    BY CERTIFICATE your_certificate 
    WITH PASSWORD = 'password protecting certificate private key';
--optionally, remove private key after signing (ephemoral)
ALTER CERTIFICATE your_certificate REMOVE PRIVATE KEY;
GO

Execute a trigger always with sa user

1 Answers1

Linked