locked
Using Sql Server Audit RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Question for some of you sql pros out there. We have a web application that we are currently using to manage users for one of our sites. We are using a fairly standard table to log changes to users records like I've seen used over and over again though the years...

    Example schema:
    CREATE TABLE [dbo].[ChangeTracker](
     [DateOfChange] [datetime] NOT NULL,
     [UserId] [int] NOT NULL,
     [ChangeId] [int] IDENTITY(1,1) NOT NULL,
     [Category] [varchar](25) NOT NULL,
     [Action] [varchar](100) NULL,
     [AffectedId] [int] NULL,
     [TableName] [varchar](50) NULL,
     [FieldName] [varchar](50) NULL,
     [ValueOrg] [varchar](255) NULL,
     [ValueNew] [varchar](255) NULL

    I'm sure you have all seen something similar. I just started researching SQL Server Audit. I have not come close to reading everything I should yet, but can anyone comment as to wether or not we might be able to replace the simple ability to show changes using this table by replacing it with the SQL Audit?

    It sounds like overkill to me but that is the question I have been posed with.

    Thanks in advance, Hal

    Monday, November 1, 2010 6:37 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

       Auditing is good to tell you who changed the data, and when, but it will not answer the “what changed” . SQL Server auditing infrastructure is design to notify about permission usage (both positive & negative attempts to access a resource).

      There is a “change data tracking” mechanims introduced in SQL Server 2008. I am personally not familiar with it first hand, but it may be worth to look at the BOL documentation on it to see if it would meet your needs. Below I am including a few links that may be helpful:

    ·         Change Tracking Overview (http://msdn.microsoft.com/en-us/library/bb933875.aspx)

    ·         Compare change data capture & Change Tracking (http://msdn.microsoft.com/en-us/library/cc280519.aspx)

    ·         Tracking data changes (http://msdn.microsoft.com/en-us/library/bb933994.aspx)

    I hope this information helps,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, November 1, 2010 7:12 PM