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. 🙂
Best Answer
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.