What, when and who? Auditing 101

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Roy Ernest » What, when and who? Auditing 101 - Part 3

12 posts, Page 1 of 2

1 2 »»

What, when and who? Auditing 101 - Part 3

Rate Topic

Display Mode

Topic Options

Author

Message

Roy Ernest

Posted Thursday, May 31, 2012 12:07 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:24 AM
Points: 3,280, Visits: 6,619

Comments posted to this topic are about the item What, when and who? Auditing 101 - Part 3

-Roy

Post #1308755

bmcateer

Posted Thursday, May 31, 2012 5:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 06, 2012 2:47 AM
Points: 1, Visits: 2

You have a typo in your code: "WriteToApllicationLog" Wink

Post #1308894

computrion

Posted Thursday, May 31, 2012 5:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:29 PM
Points: 1, Visits: 59

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

Achilies

Posted Thursday, May 31, 2012 6:00 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:08 AM
Points: 221, Visits: 375

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

Roy Ernest

Posted Thursday, May 31, 2012 7:04 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:24 AM
Points: 3,280, Visits: 6,619

bmcateer (5/31/2012)

You have a typo in your code: "WriteToApllicationLog" Wink

Thx for pointing it out. Smile

Next time I will be more careful. Smile

-Roy

Post #1308962

Roy Ernest

Posted Thursday, May 31, 2012 7:06 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:24 AM
Points: 3,280, Visits: 6,619

Hi Computrion, Thx for links. Yes, I noticed that the SQL Audit does not catch all the values.

-Roy

Post #1308963

Roy Ernest

Posted Thursday, May 31, 2012 7:08 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:24 AM
Points: 3,280, Visits: 6,619

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

jfogel

Posted Thursday, May 31, 2012 11:58 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:05 AM
Points: 308, Visits: 682

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