Recording Who Granted What With a Database Audit Specification

To answer “who granted what” later, an audit has to be running when the grant happens. SQL Server will not reconstruct that history afterward. A database audit specification records permission changes as they occur, including the ones that fail.

Why current permissions cannot tell you

A developer pings you: “I lost SELECT on a table yesterday. Who revoked it?” You can check the permissions as they are today. They tell you what is true now. They never tell you who changed it or when. Without an audit, the honest answer is a shrug.

An audit has two parts. A server audit decides where the events go, here a file. A database audit specification decides which actions in one database are recorded. Both must be switched on.

Create the audit and a demo database

This demo creates a server-level object, a server audit, and removes it at the end. First create the folder C:\Temp\SqlAuthorityDemoAudit on the server, for example with mkdir in a command prompt. The SQL Server service account must be able to write there.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;

CREATE SERVER AUDIT SqlAuthorityDemoAudit
TO FILE (FILEPATH = N'C:\Temp\SqlAuthorityDemoAudit\', MAXSIZE = 2 MB, MAX_ROLLOVER_FILES = 2)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

ALTER SERVER AUDIT SqlAuthorityDemoAudit WITH (STATE = ON);

ON_FAILURE = CONTINUE means that if the file cannot be written, SQL Server keeps running and the audit quietly loses events. Some teams prefer to stop the server instead. That is a business decision, so make it on purpose.

Record only permission changes

Now the database audit specification. It uses one action group, SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP, which covers GRANT, REVOKE and DENY on schema objects. I also create two users without logins and a small table.

USE SqlAuthorityDemo;
GO
CREATE DATABASE AUDIT SPECIFICATION DemoPermissionChanges
FOR SERVER AUDIT SqlAuthorityDemoAudit
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON);

CREATE USER DemoReader WITHOUT LOGIN;
CREATE USER DemoOther WITHOUT LOGIN;
CREATE TABLE dbo.AuditDemo (Id int);

Grant, revoke, deny, and one failed attempt

I run three permission changes as myself. Then I switch to DemoOther, who has no rights on the table, and try to grant SELECT. That attempt fails with error 15151, because DemoOther cannot even see the table.

GRANT SELECT ON OBJECT::dbo.AuditDemo TO DemoReader;
REVOKE SELECT ON OBJECT::dbo.AuditDemo FROM DemoReader;
DENY SELECT ON OBJECT::dbo.AuditDemo TO DemoReader;
GO
EXECUTE AS USER = N'DemoOther';
BEGIN TRY
    GRANT SELECT ON OBJECT::dbo.AuditDemo TO DemoReader;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
REVERT;

Read the audit file

The query waits two seconds for the queue to flush. Then it finds the current audit file and reads it with sys.fn_get_audit_file. Each row says who ran the statement, who received the change and whether it worked.

WAITFOR DELAY '00:00:02';

DECLARE @File nvarchar(4000) =
    (SELECT audit_file_path FROM sys.dm_server_audit_status WHERE name = N'SqlAuthorityDemoAudit');

SELECT a.action_id, a.succeeded, a.database_principal_name AS ExecutedAs,
       a.target_database_principal_name AS TargetUser, a.object_name, a.statement
FROM sys.fn_get_audit_file(@File, DEFAULT, DEFAULT) AS a
WHERE a.database_name = N'SqlAuthorityDemo' AND a.object_name = N'AuditDemo'
ORDER BY a.event_time, a.sequence_number;

You get four rows. Actions G, R and D are the grant, revoke and deny. Each has succeeded = 1 and was run by dbo. The fourth row is the failed grant: succeeded = 0, run by DemoOther. So an audit also catches people who tried and failed, which is often the interesting part.

Notice that ExecutedAs and TargetUser are different people. Keep both. The file also has server_principal_name and session_server_principal_name for the login behind the connection. A shared application login can stand for many humans, so an audit row is a lead, not a verdict. Success also means the statement ran, not that someone approved it.

Clean up, and mind the file

The last block turns the audit off and drops everything the demo created. The audit file stays on disk, because dropping an audit does not delete its files. Delete C:\Temp\SqlAuthorityDemoAudit yourself when you are done.

ALTER DATABASE AUDIT SPECIFICATION DemoPermissionChanges WITH (STATE = OFF);
DROP DATABASE AUDIT SPECIFICATION DemoPermissionChanges;
DROP TABLE dbo.AuditDemo;
DROP USER DemoOther;
DROP USER DemoReader;
GO
USE master;
ALTER SERVER AUDIT SqlAuthorityDemoAudit WITH (STATE = OFF);
DROP SERVER AUDIT SqlAuthorityDemoAudit;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

For real use, agree on file size, rollover and archiving with the system owner. Monitor the audit and its destination. People with high privileges can change the audit itself, so strict retention needs a copy somewhere they cannot reach. And test it now and then with a harmless change, instead of trusting the setup forever.

Switch the audit on before you need the answer.

An audit is not a time machine, it is a record that starts when you switch it on.

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