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
  • 25,255
  • 13
  • 54
  • 100
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
  • 181,950
  • 28
  • 405
  • 624
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