1

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!

0

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.

0

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:

http://colleenmorrow.com/tag/auditing/

0

You can check out the Recommended Report and Alert Designs for LOGbinder for SQL Server from Windows security expert Randy Smith. He also published some articles on SQL Server Audit Logging as well as a free tool, the LOGbinder SQL Audit Policy Wizard, to easily set up SQL Server auditing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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