Watch and log query from a user on SQL server?

Question

I need to find a way to log all scripts and queries (SELECT, INSERT, UPDATE, DELETE and EXEC) from a user.

Context: I want to check what kind of queries my support are doing on the database. At this moment I asked them to copy paste every script they run in a Teams channel but I would like to give then relief and use an automation system. These users log with their accounts on the database. I can identify them.

• alroc: Are you really using SQL Server 2008R2? If so, that may change the answers that you're given. Also, if you are, your top 20 priorities need to be migrating onto a supported version (2008R2 has been EOL for 2 years now) and not micro-managing your support staff.

Answer 1

It sounds like you want to audit user activity. You should consider using the built-in auditing feature, which was made for auditing user activity. There's a very easy tutorial for setting it up here. If you are not using an eligible edition of SQL Server, you should consider a more modern (and supported!) version, since database auditing was extended to all editions as of SQL Server 2016 SP1.

Sure, you could use Profiler (and bring your system to its knees) or Extended Events (and cry every time you need to analyze the data). Auditing has much better tooling support - and for ad hoc inspection you can just right-click, View Audit Logs, and review the data like the errorlog or SQL Server Agent history.

enter image description here

You can query the data, too, of course, using sys.fn_get_audit_file, and you can automate things around that, and the output is much easier to consume than raw XML. Just demonstrating that it's very easy to set up, and very easy to validate that it is set up correctly.