SQL Server Performance Forum – Threads Archive

Prevent new master objects.

Hi. We found this genious way of preventing new objects in the master database on SQL SERVER 2005. http://yukonxml.com/weblog/darshan/archive/2005/03/03/232.aspx We would like to do this too, but as we are running SQL SERVER 2000, we are able to only put triggers on the tables, and not the entire database. I have had a look at putting a trigger on sysobjects, but SQL SERVER does not allow me to do this. Is there a way of getting past this? Or has anyone done something similar to protect the master database against accidentially added objects? Thanks,
Martin

??? I’ve never had people "accidentally" add objects to my master database. Nobody but the system administrators (DBA’s) have have the right to add objects. There’s no way to add triggers to the tables in 2000. You could setup a trace to track DDL changes in the master database. Feed the results to a table and analyze. Perform needed actions if it occurs. We track ALL DDL changes and get a report daily on it. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Hi derrickleggett As a trainee DBA, there has been occasions where I have accidentally added a stored procedure to the master database (default when connecting to a server). As I like to take proactive measures to ensure this does not happen, I am investigating ways to avoid this, and one of the options were to add a trigger on sysobjects in master to monitor any changes. What I got so far is USE master
EXEC sp_configure ‘allow updates’, ‘1’
RECONFIGURE WITH OVERRIDE Add trigger that will notify if object is added. USE master
EXEC sp_configure ‘allow updates’, ‘0’
RECONFIGURE WITH OVERRIDE I am very aware that this is a system table and and microsoft’s advice is against this action. Then again, a trace seems like a good idea. Regards,
Martin

Really, you added that trigger in SQL Server 2000? Let me know how it worked out. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

I have not yet tried it out. 1. I’m not sure if it will work
2. I’m not sure if it is safe to do so at all. Any advice?

In order to avoid the accidental addition of objects agianst master database, you can define the default database for that user. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

It won’t work. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I was just giving you a hard time. If it’s any comfort, I’ve spent a couple hours trying to hack SQL Server to put triggers on that table. Seriously, you need to setup a trace to capture object additions to the master databae. If any are made, have it send you an email so you can correct them and find out who did it. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

]]>