Filtering SQL Server Audit Specification

Question

HI Expert,

I've set Server Audit Specifications on SQL Server with Filtered only Sysadmin Role member will be traced e.g.[server_principal_name] = 'DOMAIN\WindowsGroup'...but none members of DOMAIN\WindowsGroup was catch when they access or doing something in Databases instance

Any solution for this case?

Great appreciation for all reply

warm regards,

UrBel

Answer 1

server_principal_name in SQL Server Audit is the login name seen by the instance, not the Windows group name. Filtering on server_principal_name = 'DOMAIN\WindowsGroup' will therefore never match individual members of that group.

Key points and options:

1. server_principal_name value
   • For Windows authentication, server_principal_name is typically the individual Windows login, for example DOMAIN\User1, not the AD group.
   • When a user connects via a Windows group that has permissions, SQL Server still records the effective login as the user account, not the group.
2. Why the filter does not work
   • The predicate (server_principal_name = 'DOMAIN\WindowsGroup') only matches if the login name itself is exactly DOMAIN\WindowsGroup.
   • Members of that group connect as DOMAIN\UserX, so no audit rows satisfy the predicate and nothing is captured.
3. Practical solutions
   • Grant access via individual logins and filter on those, for example:

          (server_principal_name IN ('DOMAIN\\User1', 'DOMAIN\\User2'))
     

   • Or, if possible, create a dedicated login (e.g. DOMAIN\SqlAdmins) that is used directly and filter on that login name.
   • If the goal is to audit administrators, follow the documented guidance and audit the dbo user or the relevant admin action groups instead of trying to filter by Windows group name. Members of the sysadmin fixed server role are mapped to dbo in each database.
4. Additional considerations
   • Ensure the audit itself is enabled and attached to a Server Audit Specification that includes the relevant server-level or database-level action groups.
   • If using a predicate, confirm it is syntactically correct and enclosed in parentheses, as required.

---

References:

• SQL Server Audit (Database Engine)
• Create a Server Audit and Server Audit Specification
• Create a server audit and database audit specification
• SQL Server Audit action groups and actions
• SQL Server Audit Records