Dear all,

I am to develop an audit trailing report for a quite complex system. Every database table in my application has 4 fields - creation time, last modification time, last modified / created by and last modified from (The IP address from where that record is last updated).

Application is one which is accessible only to logged in users. Last modified / created by field stores the id of logged in user. There is only one user master from which I can easily find out the user details using ID.

Each table has got a unique primary key which is a number. This is not changed for any updation operation.

Audit trail report need only give following details.

1) Howmany times a record is modified ?

2) The time instances at which these modifications were made.

3) The person (logged in user) who made the insertion / modification / deletion

4) The IP address from where the operation is done.

5) Exact field changes made in each insertion / updation / deletion.

The usually followed method of keeping an audit trail table and having a general audit entry function is the last method I am going to employ. Reasons are

1) Changes to table structures and business logic at an average rate of one per day.

2) Difficulty in reviewing the code daily to ensure that audit entry function is properly called.

3) Code size is big and most of them are not done by me.

May I know whether there is a way to take this audit report using the SQL Server 2000 log file, and the presence of above 4 fields in each table ?

Thanks

Madhu

Madhu,

My approach to this would be to use RedGate SQL Compare (I have no association other than as a satisfied customer-- ok they did give me a free pass to VSLive back in 2003, but that was after I was a satisfied customer) and make snapshots daily so that you can make comparisons and see what has changed.

Other approach look into using the C2 audit feature on SQL Server.

Last approach wait until you upgrade to SQL 2005 where you can enable DDL triggers to fire when someone makes schema changes.

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com