I need a query which will result the usernames and query executed on databases by the users in sql server. Through which i can track which user run which query in sql server 2019 or 2022

kanav sharma 5 Reputation points
2023-10-29T21:10:09.84+00:00

I am working on sql server 2022 developer edition. for security purposes, I want to check which users run which query or on which databases. So I can keep track in the future that I have records of all users and what queries they ran.

Please help me out.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,126 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 104.2K Reputation points MVP
    2023-10-29T22:21:50.59+00:00

    First of all, to track this, you need to set things up. This is nothing that SQL Server tracks by default, as it would add quite a bit of overhead.

    There are a couple of options:

    1. SQL Server Audit.
    2. Extended events and capture the events sql_batch_starting and rpc_starting.
    3. Trace and capture the corresponding events.

    All of these can have serious impact on the throughput on a system that runs many short queries. On such a system, they would also produce too much data for it to be useful.

    Then again, if users only can access the database through the application, it makes more sense to have the auditing in the application, as the application knows what the overall operation is.

    2 people found this answer helpful.
    0 comments No comments