0

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

2
  • what are you using to monitor currently, can you ignore program_name like 'PublisherInstance_PublisherDatabase_%'?
    – Ste Bov
    Apr 12, 2017 at 10:26
  • I'm not using anything at the moment to monitor update/delete/insert statements. I was hoping to use Database Audits as I'm lucky enough to have Enterprise Edition. My main problem is that sysadm always connects to a database as dbo but also the user running the replication also needs to connect as dbo. Apr 12, 2017 at 10:58

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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