What, when and who? Auditing 101 - Part 3

Question

Post reply

What, when and who? Auditing 101 - Part 3

Roy Ernest

SSC-Dedicated

Points: 38780
More actions
May 31, 2012 at 12:07 am

#392391

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

Viewing 15 posts - 1 through 15 (of 17 total)

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

bmcateer SSC Rookie Points: 41 More actions · Answer 1

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

computrion SSC Rookie Points: 39 More actions · Answer 2

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.

Achilies SSCommitted Points: 1695 More actions · Answer 3

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.

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

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

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

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

-Roy

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

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

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 7

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

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

Yes it is. The behavior changes even for different level of Service packs unfortunately.

-Roy

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 9

jfogel

SSCarpal Tunnel

Points: 4155

May 31, 2012 at 1:31 pm

#1495500

Noted. Thanks for the article.

Cheers

n00bDBA SSCrazy Points: 2986 More actions · Answer 10

Hi,

Very nice article.

So am i right in saying this form of auditing only answers the "when" and not the "who", I saw session Id but unless your constantly recording whose using the session Id that doesn't tell you anything?

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

Who is also shown. If you look at the first http://www.sqlservercentral.com/Images/14239.jpg

you will session_server_principal_name which will show the user name

-Roy

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 12

This doesn't work for Standard edition of SQL Server.

Igor Micev,My blog: www.igormicev.com

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

That is true.. In SQL Server 2008 only EE and Datacenter supported this feature.

But in SQL 2012 Server level auditing is available in all editions. Only Database level auditing is limited to Enterprise, Developer, and Evaluation editions.

-Roy

phegedusich SSCommitted Points: 1552 More actions · Answer 14

Also, who audits the auditor? My company requires an audit tool that can't be altered by the sysadmin. So it's Guardium for now.