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.