Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

What, when and who? Auditing 101 - Part 3 Expand / Collapse
Author
Message
Posted Thursday, May 31, 2012 12:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:37 PM
Points: 2,361, Visits: 6,752
Comments posted to this topic are about the item What, when and who? Auditing 101 - Part 3

-Roy
Post #1308755
Posted Thursday, May 31, 2012 5:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 6, 2012 2:47 AM
Points: 1, Visits: 2
You have a typo in your code: "WriteToApllicationLog"
Post #1308894
Posted Thursday, May 31, 2012 5:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:10 AM
Points: 1, Visits: 78
Very nice feature and article, this will full fill so many cases but for some real auditing this needs to be get more matured.

I have two informations to share here, one is good and the other one is bad.

first the good one.
SQLCat has build some code to collect audited data, which can be modified as per need.

http://sqlcat.codeplex.com/wikipage?title=sqlauditcentral&referringTitle=Home


secondly the bad thing.
We have some limitations of seeing parameterized value, sometimes (in my case most of the time) SQL audit is unable to capture the actual value, which was passes using variable to stored procedure or sql query.


https://connect.microsoft.com/SQLServer/feedback/details/624935/sql-server-2008-database-audit-on-insert-update-and-delete-actual-sql-and-not-parameter-values



Bye.


Post #1308907
Posted Thursday, May 31, 2012 6:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:09 PM
Points: 258, Visits: 437
Can you please also make an article how to take the sql audit logs to a sql table using ssis/whichever way is easier?


Thanks.
Post #1308912
Posted Thursday, May 31, 2012 7:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:37 PM
Points: 2,361, Visits: 6,752
bmcateer (5/31/2012)
You have a typo in your code: "WriteToApllicationLog"

Thx for pointing it out. Next time I will be more careful.


-Roy
Post #1308962
Posted Thursday, May 31, 2012 7:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:37 PM
Points: 2,361, Visits: 6,752
Hi Computrion, Thx for links. Yes, I noticed that the SQL Audit does not catch all the values.

-Roy
Post #1308963
Posted Thursday, May 31, 2012 7:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:37 PM
Points: 2,361, Visits: 6,752
Beginner2008, Thx. You gave me an idea for the next article. I will try to do one regarding capturing the auditing data and writing it to a Table.


-Roy
Post #1308965
Posted Thursday, May 31, 2012 11:58 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:11 AM
Points: 372, Visits: 980
UPDATE [dbo].[tbProducts] set [ProductCost] = @1 WHERE [ProductID]=@2

For my test using 2008 R2 I actually got the values used in the update statement and not variables. I got:

update dbo.tbProducts Set ProductCost = 2750 where ProductID = 1

Is this a 2008 vs R2 thing?


Cheers
Post #1309226
Posted Thursday, May 31, 2012 1:22 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:37 PM
Points: 2,361, Visits: 6,752
Yes it is. The behavior changes even for different level of Service packs unfortunately.



-Roy
Post #1309302
Posted Thursday, May 31, 2012 1:31 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:11 AM
Points: 372, Visits: 980
Noted. Thanks for the article.

Cheers
Post #1309317
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse