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
[MyTable], the log was correctly filtered for SELECTs on
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.
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
dbo) worked well.