trigger on sysobjects

[aspijker]

Hi!

I want to know who changed or added functions or stored procedures on my databases. I was thinking of creating a trigger on updates and inserts on the sysobjects table, but I can't find out how I can see which user created them.

Cheers!

 

[LFCfan]

I presume you're on Sql Server 2000, otherwise you wouldn't be able to see the sysobjects table.

I would strongly recommend AGAINST modifying any system tables in any way.

In Sql Server 2005 you could use DDL triggers to monitor changes to procedures etc, but I'm afraid I don't know how you'd go about it in sql2000



~LFCfan

 

[aspijker]

Hi, i won't change the system table, other than adding a trigger.

 

[SQLSister]

Do not change the system table by adding a trigger. This is a very bad idea. A trigger that doesn't work correctly on a system table could take down your whole system!!!!! If you must have this functionality, upgrade to 2005 or 2008.

Personally, I think you would be better served by taking away all access to production tables except through a dba or team of dbas. No one should be making structural changes to your prodcution database except through a script. If no one has the rights except the people who are trusted to do it right (and who can be held responsible for what was done), then you don't need a trigger to know who made a change, you already know who has the access to make a change.

"NOTHING is more important in a database than integrity." ESquared

 

[aspijker]

Thanks, for your replies. I won't use it on production servers ofcourse, only DBA can access those.

But we have a lot of argueing about changes on sp's on the Development environment, it wuld be good to know after who made some changes.