locked
SQL Server audit question RRS feed

  • Question

  • I've set up a SQL server audit to capture select statements against a table in my database.   That's working great with no problems my question is when I view the selected row details of my audit entry in the audit log I see the statement has been parameterized.  Here's an example of what I mean, in a query window I execute: 

    select * from table where name = 'Smith'.  

    The audit entry displays the statement as 

    select * from table where name = @0
      My question is whether or not there is a way we can capture the where conditions in the audit event.
    Thursday, January 15, 2009 8:25 PM

Answers

All replies

  • That is by design:

    Considerations

    Database-level audit actions do not apply to Columns.

    When the query processor parameterizes the query, the parameter can appear in the audit event log instead of the column values of the query.


    (http://msdn.microsoft.com/en-us/library/cc280663.aspx)

    -Jens K. Suessmeyer
    Friday, January 16, 2009 8:56 AM
  • I would like to raise this question again. 

    How can this be considered "by design"?  

    I understand this is a known/documented "consideration".

    I would refer to it more as an "issue". 

    This makes MS SQL Server's database-level query auditing useless in some cases.  The whole intent of auditing is to track what is being done.  If "exactly" what is being done is not tracked, then auditing is not taking place, and that's an issue. 

    Is this "issue" on the drawing board to be addressed? 

    Thanks,
    Donald

    Friday, September 24, 2010 4:25 PM