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?

Michael Green
  • 24,839
  • 13
  • 51
  • 96
user2669043
  • 43
  • 3

3 Answers3

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), or you're using Query Store (which didn't exist when I first wrote this answer).

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, DMVs, Query Store, or 3rd party monitoring tools. (Examples of some of these here.)

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
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...

KrishV
  • 46
  • 3
-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.

Norbert
  • 97
  • 5
  • 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 - John M 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 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
  • @DanGuzman you have seen it, I have done it, still painful to watch it happening. And it was a filtered trace, but still no good. So I agree with all these guys, use server side traces or extended events. In case you're not familiar with XE, you can use SQLSkills proc to convert SQL Trace to XE. – Marian Mar 30 '15 at 18:01