Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

trigger on sysobjects

Status
Not open for further replies.

aspijker

Programmer
Feb 14, 2007
37
NL
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!
 
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

 
Hi, i won't change the system table, other than adding a trigger.
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top