How to audit DML statements based on server principal name

Question: I’ve set up a database audit specification based on How to capture DML for a single logon on database audit specification.   One of the problems I’ve got is I can’t seem to limit the audit to users who are in the dbo group. Any user that isn’t dbo , I can record and track.

How do I set up a database audit specification where the DML statements are based on the server_principal_name regardless of whether the user is in the dbo group

Answer:  There really is no difference in setting up an audit based on logins which are or are not in the dbo group. In fact , it’s exactly the same.

You may be experiencing some issues with the ‘\’, as quite often if it’s a domain based login you’ll need to use ‘\’. But apart from this anomaly, there should be no difference.

I’ve included for you an example specification, directly off a server and database I audit. I’ve changed the names , but essentially this is the rebuild script. The user is in the dbo group .

I confirmed the user was in the dbo group and commiting a SELECT user , logged in as that account. The response was dbo

CREATE DATABASE AUDIT SPECIFICATION [MyDBAudit]
FOR SERVER AUDIT [AuditDataAccess]
ADD (DELETE ON DATABASE::[MYDB] BY [MYDOMAIN\MYID]),
ADD (EXECUTE ON DATABASE::[MYDB] BY [MYDOMAIN\MYID]),
ADD (INSERT ON DATABASE::[MYDB] BY [MYDOMAIN\MYID]),
ADD (RECEIVE ON DATABASE::[MYDB] BY [MYDOMAIN\MYID]),
ADD (REFERENCES ON DATABASE::[MYDB] BY [MYDOMAIN\MYID]),
ADD (SELECT ON DATABASE::[MYDB] BY [MYDOMAIN\MYID]),
ADD (UPDATE ON DATABASE::[MYDB] BY [MYDOMAIN\MYID])
WITH (STATE = ON)
GO