1

Today I had to grant sysadmin rights for 2 users. ;(

As I couldn't change that, I at least want to see, what they are doing, so I am looking for a way, to audit all activities by these 2 Users. Sadly I couldn't find anything on how to set this up, with the exception of using the Profiler (which I don't want to do).

Does anybody has an idea on how to setup the Audit function to audit all activities by these 2 users?

thank you very much in advance. :)

  • 2
    The documentation is at docs.microsoft.com/en-us/sql/relational-databases/security/… - as per usual question asking guidelines you need to be more specific. What have you tried? What did not work? What output do you need that you can not see a way of achieving? Though note that as the users are SA they can probably alter the audit if they desire, unless you immediately copy the information elsewhere. Would DBO for specific databases not be suitable, instead of server-level SA? – David Spillett Oct 7 at 10:31
2

Today I had to grant sysadmin rights for 2 users.

Why?

There should be no Good Reason for anyone (except you and your Team) to have this level of access to any database. If they're having problems that they cannot resolve for themselves, then they should be working with you to solve them, not working around you and leaving you with little or no chance of fixing anything that they might break.

Always keep the biggest and best tools in the toolbox for yourself.

Trying to Audit users with this level of privilege is pointless - whatever you do, they can undo and, if they do manage to bring the whole instance crashing down, there won't be much Audit logging worth looking at anyway.

Also, bear in mind that it is your job to keep the database running, not their's. They are only interested in fixing their immediate problem, not necessarily in the overall performance and efficiency (or even continued operation) of the whole database.

At the very least, you should revoke this privilege as soon as practically possible. if you can, do it tonight before you go home so that they have to request it again tomorrow. Also, consider raising it to Management as a formal Risk to the Business Continuity Plan.

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.