Audit Logging to track database changes

Question

Post reply

Audit Logging to track database changes

Paula-196779

SSCrazy

Points: 2575
More actions
April 18, 2007 at 3:49 am

#61898

Dear all,

We are currently having some problems with someone getting into the back end of one of our systems, and deleting data from the database. I wondered if there is any way of putting an audit trail on so that I can monitor which logins are going on to the database and what they have done, i.e deletes, updates etc. The database is on SQL Server 2000.

Many thanks!

Paula

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

I would be inclined to use Profiler for this. I think that would be the fastest way you can get this set up, and normally these type of situation getting a quick result might be important. Then you can start thinking more long term logging of data after that, such as triggers to record changes (of course if they can delete data they might also be able to delete from the audit table depending on their permissions).

Is this suspected to be malicious deletes, or simply someone that does not know any better but should have delete permissions to do their job? If malicious I would start hardening up security.

ramses2nd Hall of Fame Points: 3720 More actions · Answer 2

In the past I have used a couple of methods. One would be to implement transaction log backups and then use one of the many tools out there to scrape the logs looking for specific incidents.

The second I have used is by running a trace file against the database collecting specific events and then dumping the file into a table and running queries against it. Sounds easy but there is some setup involved, plus there is the potential for some performance hit in doing this. If you're interested let me know and I can give you the scripts and code that I used to set this up.