Audit database changes

Posted on 2004-11-24
Last Modified: 2008-01-09
Is there a way of auditing all DML (insert/update/delete) changes in a database? I need to know who made the change e.g. NT User and Domain, when and what the change was and the old value.

I have tried triggers but they don't handle text/ntext fields completely.
I have looked at interpreting the log file using dbcc log(dbname,-1) but the hex data is difficult to convert 100% and its quite slow.
I have looked at Profiler and trace events but I can't see the old values and the format is poor for a client to see.

I don't want to shell out for a third party product such as Lumigent Log Explorer or Apex/Lockwood Tech as I have had problems with compatibility with third party software houses already.

Any ideas?
Question by:Code_Mania
    LVL 44

    Expert Comment

    by:Vitor Montalvão
    To know who made the insert or update you can add for each table, the column USER and create a trigger to fill that field.
    Of course that don't work with delete statement, because the row will be eliminated!

    For the olds values, you can only know if you have regular backups to restore, but that's not a good solution, because you will have a gap between backups!

    Maybe with a 3rd party software! :-|

    Good luck,

    Vitor Montalvão
    LVL 34

    Expert Comment

    If you use an Instead of Trigger, you can handle the text/ntext fields pretty accurately...

    Author Comment

    Sadly the instead of trigger has limitations with foreign keys. What does everyone else do to audit changes?
    LVL 34

    Expert Comment

    "Sadly the instead of trigger has limitations with foreign keys."

    What are the limitations you're finding?  What kind of "audit table" are you trying to insert the changes into?

    Author Comment

    I need to keep a complete record of every change to the database. The audit table should be as free form as possible. At the moment its like this:

    Table VARCHAR(50)
    Field VARCHAR(50)
    OldValue VARCHAR(500)
    NewValue VARCHAR(500)
    WhoBy VARCHAR(100)=System_User()
    When SMALLDATETIME=GetDate()

    Two problems. I don't have a complete record due to the field size of varchar(500) and the text, ntext fields in the database and instead of trigger won't cope with tables that have a foreign key (BOL) - This is per column using If Update(col) etc

    I've looked at interpretting DBCC LOG (DBname,3) and reading the record data from hex. ( Kalen Daleny's book inside SQL Server 2000 has info on table storage which is close to the log format), but its slow. Looked at fn_DBLog but there is no record data to read. This would give a complete update but has speed limitations and would need us to read the complete log daily/hourly etc to clean down old events etc.

    The only alternative I can see is to use code and work out whats happened where from ADO events.

    Author Comment

    Thanks for the suggestions.

    I have solved the problem by intercepting the WillRecordChange event in ADO which gives complete information regarding the columns that have changed. To do this I have "subclassed" the standard recordset and added an extra couple of checks and the logging method.


    Accepted Solution

    Closed, 75 points refunded.

    Community Support Moderator
    Experts Exchange

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    758 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

    10 Experts available now in Live!

    Get 1:1 Help Now