Auditing commands in SQL Server.

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

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.

Please take a look at XEvent Profiler in SSMS
https://sqlperformance.com/2019/02/extended-events/capture-queries-sql-server

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.