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)?



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2