What, when and who? Auditing 101 - Part 2

  • Thanks coetzee.jj for the compliment on the article.

    -Roy

  • First of all, excellent article.

    Now, for a question. I can see this works well for small changes. Would it be equally useful for bulk inserts? Or, we have more of a situation where we have a bulk insert and then small changes over time? I'm guessing this would still be useful, but wanted to know your thoughts.

    Thanks,

    Paul

  • It is still useful since the Bulk insert is written to the log. You can always get the changes made by the bulk insert and the subsequent changes. The change table might be large though.

    -Roy

  • Is there a trick to getting the parameter values to show up in the sql statement that was run? For this sql:

    DECLARE @lastName as varchar(50);

    SET @lastName = 'Jones';

    SELECT * FROM MyTable where lastname = @lastname;

    The audit shows this was run:

    SELECT * FROM MyTable where lastname = @lastname;

    I want to see:

    SELECT * FROM MyTable where lastname = 'Jones';

  • If I am not mistaken, if you use SQL Server 2008 SP2 , parametrized query should show the value when using SQL Audit. But this I have to check. It has been a long time since I wrote this article.

    -Roy

  • Forgot to include my version. One would think that if it works in 2008 SP2 then it would work in 2012.

    Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

    May 14 2014 18:34:29

    Copyright (c) Microsoft Corporation

    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

  • That would be my guess as well Matt. But I have seen stranger things happening. 🙂 I will look at it and give you an answer.

    -Roy

  • Thanks Roy. I can probably get what I'm looking for by creating an extended events session. Setting those up seems to be a bit more involved though. Setting up the database audit specification was pretty straight forward.

Viewing 8 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply