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

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:

  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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

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


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer