Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 31st, 2006, 04:10 AM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default Audit trailing using SQL server log file

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
 
Old April 4th, 2006, 01:11 PM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server services Log On fizzerchris SQL Server 2005 1 July 7th, 2006 08:56 AM
Viewing SQL Server Events Log(s) through .NET Ahmed_Kamran SQL Server 2000 1 December 30th, 2004 01:58 AM
SQL Server - System log full Jane SQL Server 2000 3 December 23rd, 2004 10:10 PM
SQL server log is too big. khautinh SQL Server 2000 3 September 26th, 2003 11:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.