Is there a good way to identify all queries that target a given table (extended events, procedure cache, etc)?
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.
sp_blitzcache @SlowlySearchPlansFor (First Responder Kit) appears to be an another way of doing this as well.