3

Is there a good way to identify all queries that target a given table (extended events, procedure cache, etc)?

2 Answers 2

8

One way to do it is to use the SQL Server Audit functionality. There's an example here 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.

-3

sp_blitzcache @SlowlySearchPlansFor (First Responder Kit) appears to be an another way of doing this as well.

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.