Logging Database Changes 1

[CodingIsFun]

I am trying to log all database changes.

Does anyone have any suggestions?

I have tried putting a trigger on the sysobjects table but MS SQL 2000 will not let me put a trigger on this table.

Thanks in advance for any help...

 

[JayKusch]

Good Luck! Your post reflects many others who have search for a way to do this. Triggers on production tables are a way to go but it is intensive in resources as well as space requirements to log all the data.

You cannot modify most system tables and for good reasons. One reason would be that it may cause issues w/ the way SQL Server functions and performs. Another is that when an upgrade, Service Pack or Security Patch is applied, it may very well retool a system table and thus dropping your trigger.

I am sure there are applications that can track the changes but I have not heard of too many that are cost-effective enough to bring in-house.

Thanks

J. Kusch

 

[CodingIsFun]

I am only trying to log specific events like adding and dropping tables,views and stored procedures.

Unfortunately, too many people have rights to make these changes and these types of permissions have to stay or I would just take the priveledges away.

I will keep investigating and post anything useful

thanks for the input Jay...

 

[JayKusch]

You may be able to create a temp work around by using the system views provided by INFORMATION_SCHEMA.*

You could run something like ...

select * from INFORMATION_SCHEMA.TABLES

on your databases and imsert the returning result set to a table that has a datetime field w/in the record layout. That way you could run the query later and see if all the tables that are currently returned match up w/ the records you inserted into your check table.

You could do this for a range of DB objects. Take a look at BOL (Books OnLine) for more info on "Information Schema Views".

Hope this helps some!



Thanks

J. Kusch

 

[vadimg]

this is a bit ugly, but if you're only trying to log creating/dropping database objects:

run Profiler fulltime (you can schedule jobs that to start/stop it) and capture Objecteleted, Object:Created events into a table, and run scheduled reports on it.

if you dig around in profiler, you can also figure out a way to capture events that modify objects as well (stored proc edit, etc).

of course, profiler is very intrusive, and running it fulltime on a transaction intensive production system is NOT recommended.

 

[CodingIsFun]

Information_Schema will not give me the user who is making the modification.

Vadimg's idea just might work for what I want to do.

Thanks...