02 February,2016 by Jack Vamvas
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:
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.