SQL audit log

Posted on 2014-08-22
Last Modified: 2014-08-27
Does SQL server have a way to create a audit log of all Add, Update, and Deletes?  And, I want to track these by user - and by user, I mean a user table that I have defined.
Question by:HLRosenberger
    LVL 13

    Expert Comment

    by:Russell Fox
    Starting in SQL 2008, MS added SQL Server Audit which does what you're asking, though you would have to figure out how to add in your user information. I believe that requires Enterprise Edition, though. Another solution is to only allow database changes via stored procedures, and then you can add changes to your own change log table in the proc code. I've also created triggers on the tables to add audit info to a custom audit table. Here the ObjectID comes from my own Object table, and all changed values are converted into a VARCHAR so they can all be stored in the same table:
    CREATE TABLE [dbo].[core_Audit](
    	[ID] [bigint] IDENTITY(1,1) NOT NULL,
    	[ObjectID] [int] NOT NULL,
    	[TableName] [nvarchar](50) NOT NULL,
    	[FieldName] [nvarchar](50) NOT NULL,
    	[SourceID] [uniqueidentifier] NOT NULL,
    	[DataType] [varchar](50) NOT NULL,
    	[OldValue] [nvarchar](255) NOT NULL,
    	[NewValue] [nvarchar](255) NOT NULL,
    	[UserModified] [int] NOT NULL,
    	[DateModified] [datetime] NOT NULL CONSTRAINT [DF_core_Audit_DateModified]  DEFAULT (getdate()),

    Open in new window

    And here is part of the trigger - this code block is required for each field you want to track:
    -- [OrderNumber]
    INSERT INTO dbo.core_Audit ( ObjectID , TableName , FieldName , SourceID ,DataType ,OldValue ,NewValue ,UserModified ,DateModified)
    	SELECT 22, 'core_Order', 'OrderNumber', t1.GID, 'varchar (50)', LEFT(ISNULL(CAST(t2.OrderNumber AS NVARCHAR(4000)), 'NULL'), 255), LEFT(ISNULL(CAST(t1.OrderNumber AS NVARCHAR(4000)), 'NULL'), 255), t1.UserModified, @Date
    	WHERE ISNULL(t1.OrderNumber, '')  <> ISNULL(t2.OrderNumber, '')

    Open in new window

    LVL 1

    Author Comment

    Russell - thanks.   I also found this post which is similar to your post.

    I got this to work and it records Inserts and Deletes, but not Updates.  Any thoughts why?
    LVL 1

    Accepted Solution

    ah, I found a better solution that works without error.
    LVL 4

    Expert Comment

    by:Randy Knight, MCM
    Change Tracking or Change Data Capture (depending on if you want to know the actual data) would be another option.
    LVL 1

    Author Closing Comment

    This solutions works great!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Title # Comments Views Activity
    Select distinct 25 43
    C# primary key 9 46
    (SQL) Retrieve Total results in the separate rows... 3 24
    report c# 9 56
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now