I need to audit all actions for a user on all databases – not sure best way

Posted on

Question :

I have a user on a SQL Server that is used for executing SSRS queries. This user was made a sysadmin before I started at this job. I want to remove the sysadmin permission, but not break any reports. I do not know what DDL/DML the user is actually performing.

With that said, I want to run an audit, which I have done before, but since the auditing is so fine grained, I can’t wrap my head around what all I should audit to catch the user actions on all databases.

Can anyone shed some light?

Thanks in advance!

Answer :

Colleen Morrow’s got some great resources regarding SQL Server Audit, including deployable code via PowerShell. Her articles are well worth a read and I’ve deployed her solutions in my environment to great fanfare:


Assume you want to use server-side trace to do the work (of course you can use XEvent if you have sql server 2008+)

You can create a server side trace with a filter LoginName=”target login” and then let the trace run. You can later load the trace into a table and then you can analyze all the activities of the user within a sql server instance.

Leave a Reply

Your email address will not be published. Required fields are marked *