Identify all queries that target a given table?
Is there a good way to identify all queries that target a given table (extended events, procedure cache, etc)?
briley last edited by
One way to do it is to use the https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15 functionality. There's an example https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-ver15#TsqlProcedure which I'll reproduce slightly modified in case the link goes dead in the future:
The server audit defines where you want the log file to be:
USE master ; GO -- Create the server audit. CREATE SERVER AUDIT Payrole_Security_Audit TO FILE ( FILEPATH = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA' ) ; GO -- Enable the server audit. ALTER SERVER AUDIT Payrole_Security_Audit WITH (STATE = ON) ;
The database level audit lets you pick specific objects, operations, and users:
USE AdventureWorks2012 ; GO -- Create the database audit specification. CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables FOR SERVER AUDIT Payrole_Security_Audit ADD (SELECT , INSERT , UPDATE, DELETE ON HumanResources.EmployeePayHistory BY public) WITH (STATE = ON) ; GO
The "public" user should catch queries from anyone.