Post reply

What, when and who? Auditing 101 - Part 2

  • December 12, 2014 at 6:09 am

    #1764379

    Thanks coetzee.jj for the compliment on the article.

    -Roy

  • December 12, 2014 at 6:49 am

    #1764397

    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

  • December 12, 2014 at 7:24 am

    #1764417

    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

  • December 15, 2014 at 12:39 pm

    #1764782

    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';

  • December 15, 2014 at 1:17 pm

    #1764790

    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

  • December 15, 2014 at 2:53 pm

    #1764806

    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)

  • December 16, 2014 at 4:33 am

    #1764887

    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

  • December 17, 2014 at 7:27 am

    #1765179

    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 22 (of 22 total)

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