[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 524
  • Last Modified:

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?
0
Code_Mania
Asked:
Code_Mania
1 Solution
 
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
0
 
arbertCommented:
If you use an Instead of Trigger, you can handle the text/ntext fields pretty accurately...
0
 
Code_ManiaAuthor Commented:
Sadly the instead of trigger has limitations with foreign keys. What does everyone else do to audit changes?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
arbertCommented:
"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?
0
 
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:

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.
0
 
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.



0
 
moduloCommented:
Closed, 75 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now