How to capture DML for a single logon on database audit specification

Question: I’ve read the How to Audit user DML with SQL Server Extended Events   and set up the sql server and database audit specification successfully.

I’m now capturing DML access to specific database data objects for all users.  I’d like limit the  audit capture  details to  a specific logon , not for all logons.

The events I’d like to capture in the audit are:

SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, REFERENCES

How can I make this happen?

Answer: It is possible to limit the audit to a single logon. You’ll still need to set up the server audit specification in the same way as on How to Audit user DML with SQL Server Extended Events , but you’ll need to make a change to the database audit specification.

The key adjustments to make are:

1. Add more DML types to capture. In the example below, I've added a number of SQL commands
2. After the database name add the user. In this example the user is called “test”

 CREATE DATABASE AUDIT SPECIFICATION [privatedata]
FOR SERVER AUDIT AuditDataAccess
	 ADD (SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, REFERENCES 
ON DATABASE::AuditDB BY test)

 To test whether this works - you'll need to use two different logons. The first logon will be the logon you specified in the CREATE DATABASE AUDIT SPECIFICATION. The second logon will be a logon that is not part of the filter . logon on with both logons, confirm via checking the log file.

Read more on audit and database security

Powershell sql server security audit - SQL Server DBA

How to create a SQL Server Security Audit - SQL Server DBA

Security audit for SQL public role - SQL Server DBA