• Sreejith! - Wednesday, May 24, 2017 3:04 PM

    Hello,

    I am trying to audit SELECT statement on specific tables.SQL server is 2008 R2 standard edition(Enterprise edition auditing tools are not available)
    I am able to audit all SELECT across the all databases with the help of below script
    https://gallery.technet.microsoft.com/Auditing-SELECT-Statements-0c476b83

    But when I tried to add below additional filters for DB and tables, it is not working at all.

    exec sp_trace_setfilter @TraceID, 35, 0, 6, N'%DB Name%'

    exec sp_trace_setfilter @TraceID, 34, 0, 6, N' Table name1'
    exec sp_trace_setfilter @TraceID, 34, 0, 6, N' Table name2'

    Can someone please help?

    Thanks

    Not all columns are available for all events and object name isn't available for SQL:StmtStarting.
    You would need to filter on the text data for the object names and for logical operator, you would need to use OR on the second table if you wanted Table1 or Table2. So for your filters try something along the lines of this if you want select statements for Table1 or Table2 in YourDatabase:
    exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%SELECT%'
    exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%Table1%'
    exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%Table2%'
    exec sp_trace_setfilter @TraceID, 35, 0, 6, N'%YourDatabase%'

    For the filters, you can find more information in this topic:
    sp_trace_setfilter (Transact-SQL)

    And then check the event class to see which columns are available. So in your case, refer to this article:
    SQL:StmtStarting Event Class

    The example you are using is using the event above and then filtering the column text data using like '%SELECT%'
    Sometimes it's easier to just open up profiler and setup what the example has and then setup your own filters and export the trace definition. If you wanted to try that, just create the trace with SQL:StmtStarting and add the filters you want. You have to start a trace to export the definition so then start it and stop it right away. Then go to the menu, to File, to Export and Export Trace Definition to save it off as a sql file.

    Sue