SQL Server Audit Filtering Issues at Database Level

sql serversql server 2014sql-server-2012

I have SQL Server 2014 Enterprise Edition installed. Due to privacy considerations, SELECT statements on private data need to be logged, except for a specific domain user, used by a service which writes to the database.

Creating a database level audit specification works well. E.g.

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-01]
FOR SERVER AUDIT [My_Audit]
ADD (SELECT ON SCHEMA::[dbo] BY [public])
WITH (STATE = OFF)
GO

causes all SELECT statements from everyone to be written to the log file. When I replaced SCHEMA::[dbo] with [MyTable], the log was correctly filtered for SELECTs on MyTable only.

But when I replaced public by specific user name MYDOMAIN\myuser, the log entries are not filtered for the given user name as I would have expected.

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-01]
FOR SERVER AUDIT [My_Audit]
ADD (SELECT ON SCHEMA::[dbo] BY [MYDOMAIN\myuser])
WITH (STATE = OFF)
GO

Every SELECT issued by any user was logged instead.

I queried sys.server_principals to check whether the given user was a valid principal name, but it actually showed up in the result.

What is wrong with my second audit specification?

Edit: It seems like if the CREATE statement in question has been silently discarded. When issuing Script Database Audit Specification as … in SQL management studio, the original statement is written to the query editor window.
However, changing the value to others than user names (e.g., replacing public by dbo) worked well.

Best Answer

In my case, MYDOMAIN\myuser was a server principal, not a database principal. Only the latter ones can be used in database audit definitions.

Database principals can be either database users, or roles (see documentation). I created a new database role to be used in the audit definition. Then I created the logins for the users in question and chose the apt role membership under User mapping.

Hint: For some reasons, it seems necessary to define the role prior to adding the logins for the users. Adding existing logins as role members did not work for me.

Related Question