July 8, 2002 at 7:28 am
I have been trying to figure out a way to audit some databases in SQL Server 2000. I want to have some sort of log or table that keeps track of who updates/deletes/inserts, etc. I haven't been able to figure out how to do this. Anyone have any ideas on how to go about doing this?
July 8, 2002 at 7:41 am
The most direct is to create a server side trace that captures the info you need and writes it to a file. Then you can periodically (if you want) pick up the files and import into a table for analysis. Easiest way to build the trace is to run Profiler and experiment until you get the info you need, then do Save Trace and it will generate a script you can use to build a stored proc/job that you can run on server start up.
Andy
July 8, 2002 at 9:46 am
The only other (easy) way to do this is a bunch of triggers on all tables that insert the data into other tables. There are some tools (I think Lockwood has one. Look for ads on this site) that will do this.
You can also get a copy of Log Explorer from Lumigent and this will let you read (and audit) the transaction logs.
Steve Jones
July 13, 2002 at 2:02 pm
Just keep in mind that a trace is the only way you will see SELECT, TRUNCATE and other nonlogged style functions.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 13, 2002 at 5:38 pm
That's a good point! Wish I woulda said it! Ah well. Anyway, I agree with Steve that triggers do a good job of capturing data changes and are pretty easy to implement. I think one thing you have to look at is how often do you plan to look through the history? If you just want it case of problem, to prove something, etc, I think a trace is easier. If you're going to refer to it frequently less headache to track changes on a table by table basis.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply