What, when and who? Auditing 101 - Part 3

  • Roy Ernest

    SSC-Dedicated

    Points: 38707

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

    -Roy

  • bmcateer

    SSC Rookie

    Points: 41

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

  • computrion

    SSC Rookie

    Points: 39

    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: 1549

    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: 38707

    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: 38707

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

    -Roy

  • Roy Ernest

    SSC-Dedicated

    Points: 38707

    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: 4043

    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: 38707

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

    -Roy

  • jfogel

    SSCarpal Tunnel

    Points: 4043

    Noted. Thanks for the article.

    Cheers

  • n00bDBA

    SSCrazy

    Points: 2986

    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: 38707

    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: 33109

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

    Igor Micev,
    My blog: www.igormicev.com

  • Roy Ernest

    SSC-Dedicated

    Points: 38707

    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

    Ten Centuries

    Points: 1342

    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.

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

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