Audit sysadm users

Question

I have a SQL 2014 Server that has several transactional replicated databases that are used for reporting purposes. Also on that server there is a windows group containing several local local people. This group has sysadm privileges (mainly so that the local people can schedule and edit each others agent jobs).

I need to monitor activity on the replicated databases by the members of this group.

I've tried setting up a database level audit and specifying from the dbo group. This works to a certain extend up has the unfortunate side effect of also picking up all of the replicated transactions as the user running the replication of also a dbo of these databases.

Is there any way of excluding a particular user from an audit? I've tried mapping this group as a user to one of the databases but the audit failed to pick up anything

Answer 1

I found an answer to this:

Create an Audit as disabled Create a Database Audit Specification and link it to your audit. Add INSERT, UPDATE and DELETE and select either the database or required objects or schema. In the principal field select DBO

Use the following statement to alter the Audit

ALTER SERVER AUDIT [Audit Name] WHERE server_principal_name <> N'domain\serviceaccount';

Enable the Audit and Specification

This will now capture all INSERTS/UPDATES/DELETES from the dbo use (sysadm always maps to a database as dbo) excluding the specified service account.