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!
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.