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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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
If you use an Instead of Trigger, you can handle the text/ntext fields pretty accurately...
Code_ManiaAuthor Commented:
Sadly the instead of trigger has limitations with foreign keys. What does everyone else do to audit changes?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

"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_ManiaAuthor Commented:
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()

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_ManiaAuthor Commented:
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.

Closed, 75 points refunded.

Community Support Moderator
Experts Exchange

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.