asked on

Audit database changes

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?

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

arbert

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

Code_Mania

ASKER

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

arbert

"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?

Code_Mania

ASKER

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:

ID INT IDENTITY
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.

Code_Mania

ASKER

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.

ASKER CERTIFIED SOLUTION

modulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial