2

I have a database in which most of tables are readable for users in one particular security group.

I know most of the users are from my team, but lately I found that it's been used by more and more people, and most of execution occurs from Excel and/or Access.

Out of curiosity, is there any way for me to track who has been tapping into the SQL server and what query they have been running?

7

Queries aren't logged by SQL Server, unless they meet some criteria that puts them into the default trace or system_health Extended Events session (some details on what they collect here).

If you can control data access via stored procedures, you can easily add your own logging to those. But that doesn't sound like the case. Otherwise you will have to rely on triggers (for DML, but not SELECT), server-side trace, extended events, auditing, or 3rd party monitoring tools.

| improve this answer | |
0

I would agree with Aron's idea here....

Extended Events can get you all the information you needed.

or

Querying from DMV's directly would be an alternative. DMV's have the information from the last time when sql server was restarted...

| improve this answer | |
  • what options does this offer that Aaron didn't cover in his answer? – LowlyDBA Mar 30 '15 at 18:00
  • John - I missed Aaron's response before... and I had to modify my comment.... It was pretty much the same... – KrishV Mar 30 '15 at 18:26
-2

You can setup the sql profiler and start recording all your queries into a table. Filter the table for the host and see which host, and possibly which users are doing this.

| improve this answer | |
  • SQL Profile is deprecated and a bit of a resource hog. It's advised to use the replacement, Extended Events, to perform traces. (not my downvote btw) – LowlyDBA Mar 27 '15 at 20:54
  • 2
    A server side trace would be much better option then using Profiler. See this and this for more details. – Kin Shah Mar 27 '15 at 21:03
  • @user2669043 : So 3 options already to get insight in who is doing what. It will take some work. John : "a bit of a resource hog" is an understatement. The profiler tool however is very easy to use, so nice for the owner of the question to start with. – Norbert van Nobelen Mar 27 '15 at 21:07
  • 2
    Disagree - profiler is a production disaster waiting to happen, plus it's deprecated. Why start them off with something that can kill their server now and that they'll have to abandon for something else later anyway? – Aaron Bertrand Mar 27 '15 at 23:00
  • 1
    @NorbertvanNobelen, I have seen a busy OLTP production server crash immediately after a DBA started an unfiltered Profiler trace of batch and rpc events due to worker thread starvation. An unattended trace (SQL Trace or Extended Events) will avoid this. – Dan Guzman Mar 30 '15 at 11:59

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.