0

A Sql Server 2012 Database Audit performs an audit on sysadmins when you specify the principal DBO. However, this causes ALL the sysadmins to be audited. What configuration or solution can be used to audit ONLY 1 specific group that has sysadmin membership without auditing all the activity by ALL sysadmins? We are trying to cut down on action events that are not necessary (i.e. sql engine service account or other service accounts that are sysadmin and do not need audited). This is a user database.

1

One option may be to add individual sysadmins as users to the databases in question, add those users to a custom role with no explicit permissions (effectively like public), and setup the database audit specification to track actions for the custom role.

| improve this answer | |
  • The limitation with SQL Server auditing is that the principal specified to audit users or groups that are members of the sysadmin role must be set to DBO. This causes all DBO DML to be audited, which is too much. Any group or user that is a member of sysadmin does NOT audit/log if that group or user name is set as the principal. Principal must be set to DBO for auditing sysadmins. – James Bailey Apr 4 '17 at 16:25
0

https://blogs.msdn.microsoft.com/sqlsecurity/2012/10/03/filter-sql-server-audit-on-action_id-class_type-predicate/

In SQL Server 2012, Server Audit can be created with a predicate expression (refer to MSDN). This predicate expression is evaluated before audit events are written to the audit target. If the evaluation returns TRUE the event is written to the audit target else it’s not. Hence one can filter audit records going to the audit target based on the predicate expression.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.