What, when and who? Auditing 101 - Part 2

Question

Post reply

What, when and who? Auditing 101 - Part 2

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Thanks coetzee.jj for the compliment on the article.

-Roy

Paul Liadis SSC Veteran Points: 239 More actions · Answer 2

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

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

matt.huard SSC Enthusiast Points: 158 More actions · Answer 4

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

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

matt.huard SSC Enthusiast Points: 158 More actions · Answer 6

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)

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

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

matt.huard SSC Enthusiast Points: 158 More actions · Answer 8

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.