Permissions on Trigger Problem

I am working with a database in MS-SQL 2008 R2, and I'm running into a problem.

The basic setup is this: There are two tables of interest: dbo.Assesments and audit.Assesments. dbo.Assesments is the working data table, and has triggers for data changes (insert, update, delete) to update audit.Assesments with a log of changes. As far as I can tell, this has worked for years, so far so good.

Our L2 support has a Role called SupportRole that they get into with WinAuth, and that role has some select rights and execute rights for stored procedures. One of those stored procedures does an update to dbo.Assesments. This is where the problem comes in: The update goes in, the stored procedure fires to insert a audit row to audit.Assesments, and then they promptly get a "Insert denied, insufficient rights to Insert", the transaction roles back, etc.

This is mystifying to me: I had thought triggers were more like stored procedures, where if they are made they have the rights to do whatever they are doing, but this has me thinking twice. I don't want to give insert rights to the support role for the audit table/schema. What should I do?

Triggers run, by default, under the security context of the principal who caused the trigger to fire.

In order to change this behavior, you'll need to create the trigger using the WITH EXECUTE AS OWNER clause.

Below is an example which shows how that works. WITH EXECUTE AS OWNER allows the trigger to run in the security context of the triggger owner, instead of the principal who is updating the table. From the docs:

OWNER

Specifies the statements inside the module executes in the context of the current owner of the module. If the module does not have a specified owner, the owner of the schema of the module is used. OWNER cannot be specified for DDL or logon triggers.

First, we create a test table:

USE tempdb;
IF OBJECT_ID('dbo.t') IS NOT NULL
BEGIN
    DROP TRIGGER t_trig;
    DROP TABLE dbo.t;
END
GO
CREATE TABLE dbo.t
(
    ID INT NOT NULL
    , ID2 INT NULL
);
GO

Here's the trigger code, with EXECUTE AS OWNER:

CREATE TRIGGER t_trig ON dbo.t
WITH EXECUTE AS OWNER
AFTER INSERT
AS
BEGIN
    UPDATE dbo.t
    SET ID2 = ID
    WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.ID = dbo.t.ID);
END
GO

Now, we'll create a test login with low privileges that can be used to test the hypothesis that EXECUTE AS OWNER allows an under-privileged principal access to functionality they otherwise would not have:

CREATE LOGIN tLogin WITH PASSWORD = 'QWERFsdf23454%';
CREATE USER tLogin FROM LOGIN tLogin WITH DEFAULT_SCHEMA = dbo;

We'll give them the ability to insert rows into dbo.T, but prevent them from running UPDATE statements:

GRANT INSERT ON dbo.t TO tLogin;
DENY UPDATE ON dbo.t TO tLogin;

Here we do the test:

EXECUTE AS USER = 'tLogin';
/*
    Output here shows we're running under the tLogin
    security context
*/
SELECT SUSER_SNAME();
/* 
    This will fail, with insufficient privileges
    since we've DENY'd the UPDATE privilege to tLogin.
*/
UPDATE dbo.t SET ID2 = ID; 
/*
    this will run the INSERT since the trigger 
    has EXECUTE AS OWNER
*/
INSERT INTO dbo.t(ID) VALUES (1); 
/*
    This takes us out of the tLogin security context
*/
REVERT

Here we can see the row in dbo.T has the changes made by the trigger:

SELECT *
FROM dbo.t;

And here, we cleanup the low-privilege user:

DROP USER tLogin;
DROP LOGIN tLogin;

The output from running the above is:

StackExchange Database Administrators Q#135793, answer score: 14