Auditing database activity
-
I am not a DBA, but I find myself in the role of one today. I need to audit a SQL Server 2000 database to track who altered what tables, renamed what fields, etc. Could someone point me to a good tutorial? Or recommend a 3rd-party tool? Would prefer to learn how to do it myself.
Thank you,
--Rob
-
congrats!
good luck
-
this site is driving me nuts with it's losing what you just typed when you try to preview!!!!!!grrr
- Anyway -
I've never done this, so it may be wrong.
Create triggers on the sys tables in your user database you would like to track. You'll need to learn a bit about the system tables and how things are stored. You'll write a trigger which will insert any new, updated or deleted records along with a date and user id, into an audit database. Write some reports on that db. Deploy these triggers to all your databases. Then perhaps deploy them to your model db so all new user dbs will have them in place.
Most pre made solutions are meant to track what users do to user data, not schema changes. SQL 2005 has some reports which show schema changes - nice!!
-
I found this link that explains how to use C2 auditing.
http://www.databasejournal.com/features/mssql/article.php/3399241
-
Very cool. I see it puts all the data in a trace file. I'd still want to get those into a table.
"SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc', default)GO"
I haven't done sp's dealing with file structures, but you could probably make a sp which checks your directory nightly and loads all of the .trc files into a table for reporting. I'd be curious to see what C2 gathers.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlc2.mspx <- it's all there!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply