2

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.

1
  • 3
    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.
    – alroc
    Sep 3 at 10:53
4

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.

2

EDIT: As @alroc suggested in the comments. SQL Server Profiler, it is ancient technology and you can use Extended Events. There are many discussion which is better and why, so for further information you can check: When should extended events be used instead of SQL Profiler/perfmon? which gets shipped with the SQL Studio Express application of Microsoft.

If you want a query, I think this might work for you , but you have to make some changes to it based on you users.

SELECT      c.session_id, s.host_name, s.login_name, s.status, st.text, s.login_time, s.program_name, *
FROM        sys.dm_exec_connections c
INNER JOIN  sys.dm_exec_sessions s ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS st
ORDER BY    c.session_id
2
  • 3
    Please don't use Profiler. It's ancient technology at this point, has not been updated with newer functionality in SQL Server, and puts strain on the server it's monitoring. It's been replaced by Extended Events.
    – alroc
    Sep 3 at 10:51
  • 1
    @alroc that is correct I updated my answer adding further information about this topic Sep 3 at 10:56

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.