How to audit DML statements based on server principal name

11 March,2016 by Jack Vamvas

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


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 audit DML statements based on server principal name


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