Audit Triggers That Record the Real User Behind EXECUTE AS

Audit triggers often record dbo for every change, and that tells an auditor nothing. The fix is simple. Store several identities side by side, so the audit row can answer who really did it.

Why the audit table says dbo

Picture a call from the finance team. A price changed last Tuesday and nobody owns up to it. You open the audit table and every row says dbo. Great. That narrows it down to everyone.

This happens because a trigger runs in the context of whoever, or whatever, executes the statement. If the change came through a procedure written WITH EXECUTE AS OWNER, the context is the owner, which is dbo. The person who called the procedure is hidden.

Let me build a small demo so you can see it. The demo creates a database named SqlAuthorityDemo and drops it at the end.

Create a table and an audit table

The audit table keeps the old and new price, a UTC timestamp, and four identity columns. Each identity column answers a different question, and I will explain them as we go.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.PriceList (ItemId int PRIMARY KEY, Price decimal(12,2) NOT NULL);

CREATE TABLE dbo.PriceAudit
(
    ItemId          int            NOT NULL,
    OldPrice        decimal(12,2)  NULL,
    NewPrice        decimal(12,2)  NULL,
    ChangedAtUtc    datetime2      NOT NULL,
    DatabaseUser    sysname        NOT NULL,
    CurrentLogin    sysname        NULL,
    OriginalLogin   sysname        NOT NULL,
    ApplicationUser nvarchar(128)  NULL
);

INSERT dbo.PriceList (ItemId, Price) VALUES (1, 10), (2, 20);

Write the trigger

A trigger fires once per statement, not once per row. So join inserted and deleted on the key. Do not copy values into scalar variables, or a multirow update will log only one row.

CREATE OR ALTER TRIGGER dbo.PriceList_Audit ON dbo.PriceList AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.PriceAudit
        (ItemId, OldPrice, NewPrice, ChangedAtUtc,
         DatabaseUser, CurrentLogin, OriginalLogin, ApplicationUser)
    SELECT i.ItemId, d.Price, i.Price, SYSUTCDATETIME(),
           USER_NAME(), SUSER_SNAME(), ORIGINAL_LOGIN(),
           CONVERT(nvarchar(128), SESSION_CONTEXT(N'ApplicationUser'))
    FROM inserted AS i
    JOIN deleted AS d ON d.ItemId = i.ItemId;
END;

USER_NAME is the database user the code runs as. SUSER_SNAME is the login for the current context. ORIGINAL_LOGIN is the login that opened the connection, even after impersonation. SESSION_CONTEXT is a label that your application sets on its own.

Update the table directly

First the simple case. The application sets a label right after it connects, then updates both rows in one statement. You get exactly two audit rows, one per item: 10 became 11 and 20 became 21.

EXEC sys.sp_set_session_context @key = N'ApplicationUser', @value = N'DemoUser';

UPDATE dbo.PriceList SET Price = Price + 1;

SELECT ItemId, OldPrice, NewPrice, DatabaseUser, CurrentLogin, OriginalLogin, ApplicationUser
FROM dbo.PriceAudit
ORDER BY ItemId;

In my run, DatabaseUser is dbo and ApplicationUser is DemoUser. The two login columns hold names from your own machine, so yours will differ from mine. Look at them in your output, but keep your eye on DatabaseUser. That column is about to change its story.

Now go through a procedure with EXECUTE AS OWNER

Many applications never touch tables. They call procedures. Here a caller with no login and no table rights runs a procedure that updates the price list as the owner. I clear the audit table first so the result is easy to read.

CREATE OR ALTER PROCEDURE dbo.RaisePrices WITH EXECUTE AS OWNER
AS
    UPDATE dbo.PriceList SET Price = Price + 1;
GO
CREATE USER ReportCaller WITHOUT LOGIN;
GRANT EXECUTE ON dbo.RaisePrices TO ReportCaller;
GO
TRUNCATE TABLE dbo.PriceAudit;

EXECUTE AS USER = N'ReportCaller';
SELECT USER_NAME() AS CallerDatabaseUser;
EXEC dbo.RaisePrices;
REVERT;

SELECT ItemId, OldPrice, NewPrice, DatabaseUser, CurrentLogin, OriginalLogin, ApplicationUser
FROM dbo.PriceAudit
ORDER BY ItemId;

The first result says ReportCaller. The audit rows say dbo anyway. That is the trap. If DatabaseUser were your only identity column, you would never learn who called the procedure.

OriginalLogin still holds the login that opened the connection, and ApplicationUser still holds DemoUser. Those two columns are what save you. But OriginalLogin is only useful when each person has their own login.

What if everyone shares one login?

Most web applications connect with one service account. Then OriginalLogin is the same for every person, and the business user lives only in the label. If the application forgets to set it, you get NULL.

EXEC sys.sp_set_session_context @key = N'ApplicationUser', @value = NULL;

TRUNCATE TABLE dbo.PriceAudit;
UPDATE dbo.PriceList SET Price = Price + 1 WHERE ItemId = 1;

SELECT ItemId, DatabaseUser, ApplicationUser
FROM dbo.PriceAudit;

NULL is honest, at least. Make the application set the label from the authenticated request, every time a pooled connection is reused. Then test that path before you trust the column.

Know what a trigger cannot promise

A trigger is ordinary code, so the owner can disable it. Watch what happens when someone does. The update succeeds and the audit table stays empty.

TRUNCATE TABLE dbo.PriceAudit;

DISABLE TRIGGER dbo.PriceList_Audit ON dbo.PriceList;
UPDATE dbo.PriceList SET Price = Price + 1;
ENABLE TRIGGER dbo.PriceList_Audit ON dbo.PriceList;

SELECT COUNT(*) AS AuditRows FROM dbo.PriceAudit;

Also remember the audit insert is part of the same transaction. If it fails, your update fails. Test multirow statements and busy periods before you put this on a hot table. For strict compliance, look at SQL Server Audit or ledger tables instead.

Finally, remove the demo database.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Run it once with your own login, then read the audit rows with the application owner.

An audit trigger is not one name in a column, it is context for the change.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.