Auditing commands in SQL Server.

Doria 1,246 Reputation points
2020-09-01T02:20:17.567+00:00

Hi everyone!

In addition to the SQL Server Profiler tool, what is another possible way / tool to capture the T-SQL command that a given table object receives? Is it possible to create a trigger associated with the table that captures incoming commands?

Thanks.

Developer technologies | Transact-SQL
SQL Server | Other
{count} votes

Accepted answer
  1. SQLZealots 276 Reputation points
    2020-09-02T02:56:04.447+00:00

    I would not suggest TRIGGER for tyour case as it may have an impact on performance.

    Extended Events (may be new to you) is very efficient way, Server side trace is also looking good option in your case.
    https://www.sqlservercentral.com/blogs/setting-up-a-server-side-trace


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
    Blog
    LinkedIn

    0 comments No comments

5 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,221 Reputation points
    2020-09-01T02:57:40.533+00:00

    Hi @Doria ,

    You could consider to use Extended Events , SQL Trace or Database Audit to capture what you want.

    In addition, you could also create a trigger to capture it. If one table is inserted/deleted/updated, you could receive one message, insert into another table or send an email.

    If the response is helpful, please click "Accept Answer" and upvote it.

    Best regards
    Melissa

    1 person found this answer helpful.
    0 comments No comments

  2. Shashank Singh 6,251 Reputation points
    2020-09-01T05:17:01.097+00:00

    If you want to know the history of queries that accessed your table you could use below query. Copied from This Source

     SELECT
     qryStats.last_execution_time AS [Time]
     ,qryText.TEXT AS [Query]
     ,DB_NAME(qryText.[dbid]) AS [Database]
     ,OBJECT_NAME(qryText.[objectid]) AS [TableName]
     FROM sys.dm_exec_query_stats AS qryStats
     CROSS APPLY sys.dm_exec_sql_text(qryStats.sql_handle) AS qryText
     WHERE qryText.TEXT LIKE '%TableName%'
     ORDER BY qryStats.last_execution_time DESC
    

    If you want to check currently executing queries hitting your table

    SELECT
     z.start_time
     ,z.command
     ,z.status
     ,x.text
     ,USER_NAME (z.user_id) [User Name]
     ,DB_NAME(z.database_id) [Database]
     ,OBJECT_NAME(x.objectid) [Table Name]
     FROM sys.dm_exec_requests z
     CROSS APPLY sys.dm_exec_sql_text(z.sql_handle) x
     WHERE x.text LIKE '%TableName%'
     ORDER BY z.start_time DESC
    

    The best would be to create extended events trace. The 2 queries would not give you absolute information but are helpful to identify the queries.

    0 comments No comments

  3. Uri Dimant 211 Reputation points
    2020-09-01T10:49:35.567+00:00
    0 comments No comments

  4. Doria 1,246 Reputation points
    2020-09-02T02:46:39.613+00:00

    Thanks for all the answers!

    But before I venture into Extended Events, which seems a little complicated to me, is it possible to write a trigger that logs in another table the T-SQL code that hit the table object at that moment, as well as the user's login?

    I have never seen a trigger with such a T-SQL code. Some example would be welcome.

    Regards.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.