No More Stray User Tables in Master

If you are a database admin or a developer with DBA privileges, you are probably familiar with this problem: rogue objects in “Master”. Take a look at your Master DB. Browse the catalog. You will see some tables and stored procedures that don’t belong there. Maybe, as you read this, you chuckle to yourself and say, “no, that doesn’t happen to me”, but if you take a moment to check right now, you might be surprised (or maybe not so much).

“Okay, who is the wise guy who is putting user Tables and Stored Procedures in the Master database?!” Then, you take a closer look and realize: “oh, I just remembered the one time, last month, when I ran my DDL scripts and it didn’t seem to create the objects. They just never appeared. It was very mysterious.” Well, mystery solved. The Master database often seems to be the “Lost & Found” bin of the SQL Server world.

Before I talk about strategies for preventing this, let’s consider the cause.

The primary reason that this happens is because every new user account is assigned a default database, when it is created. So, when you (or anyone) log in to SQL Server, you are pointed at your default database automatically. If you don’t switch to another database, all of your commands go to your default database. SQL Server uses the Master database as the default(default) database for every user. It doesn’t seem like a good idea, but usually, there is not really a better default(default) database to use.

Okay, so we know why this happens. There are two popular ways for preventing this kind of problem.

The first approach is: When a new user account is created (or changed), set the default database to something else (not Master or any other system database). In fact, this is considered to be a “best practice”. However, most DB admins don’t bother to set a different default database for each user, because it is tedious and it seems like the users will ask you to change it, periodically. It seems like more of a hassle than it is worth. If you have a lot of database users, this kind of thing could maybe even turn into a full-time job.

Even if you only have a few users, there is still one main down-side to this approach: Think about your developers who are generating objects in the wrong database. They just don’t pay enough attention. Right now, these stray objects are all going into the Master database (aka. Your lost & found bin). So, when you set their default database to something else, then that will become their new lost & found bin. It might be the right place or not. You may never know. So sometimes, this cure might be worse than the problem. Instead of one lost & found bin, each DB could become a separate (distributed) lost & found bin. Good luck keeping up with that.

The second approach is: Leave Master as the default for developers, but block them from installing new objects into the Master database. When a developer accidentally runs a CREATE TABLE script against Master, wouldn’t it be better to block it and show the developer a warning message? “YOU SHALL NOT PASS!!!”

This is accomplished via a little magic called a DDL trigger. It runs at a server level and can apply to a table, a database, or the entire server:

-- This makes sure your system trigger only applies to
--  the Master DB and not some unintended DB.
USE Master
GO

CREATE TRIGGER You_Shall_Not_Pass
ON DATABASE
-- Yes, it is literally supposed to say "ON DATABASE" and
--  not the name of an actual database like "Master", etc.
FOR DROP_FUNCTION, ALTER_FUNCTION, CREATE_FUNCTION,
DROP_INDEX, ALTER_INDEX, CREATE_INDEX,
DROP_PROCEDURE, ALTER_PROCEDURE, CREATE_PROCEDURE,
DROP_SCHEMA, ALTER_SCHEMA, CREATE_SCHEMA,
DROP_SYNONYM, CREATE_SYNONYM,
DROP_TABLE, ALTER_TABLE, CREATE_TABLE,
DROP_TRIGGER, ALTER_TRIGGER, CREATE_TRIGGER,
DROP_VIEW, ALTER_VIEW, CREATE_VIEW,
RENAME
-- For the full list of possible actions to block, see
--  the <a href="http://msdn.microsoft.com/en-us/library/bb522542(v=sql.105).aspx" target="_blank">MSDN library docs on DDL triggers.</a>
AS
PRINT 'You have been prevented from working with Table objects in the Master database.
Chances are, you intended to run this script in another database, 
but you forgot to change your connection.
If you ACTUALLY need to run this DDL against the Master database, 
you will need to disable a Trigger named "You_Shall_Not_Pass".
It is strongly advised that you reconsider your actions and DO NOT mess 
with anything in database Master! Remember, this DDL trigger was put in place for your protection. 
If you damage the SQL Server, it might affect your next bonus check. 
Certainly it will exempt you from receiving any kind of decent fruit-basket this Christmas. 
Think of how sad your holidays will be, just because you would not listen. 
Enough said. You have been warned.'
ROLLBACK

-- If you would like to use this DDL Trigger more like a trip-wire, you could record
--  the name of the person messing with Master and the attempted actions.
-- Try reading-up on the EVENTDATA() system function. It returns a block of XML
--  containing lots of good information on what is happening. That kind of information 
--  could come in handy if you have someone on your team who does not seem to follow
--  directions very well or doesn't seem to care enough.
;

To turn-off this super cool mechanism (presumably because you have lost your darn mind and have stopped caring about the integrity of your server or something), you can run this command to turn this trigger-off, temporarily:

DISABLE TRIGGER You_Shall_Not_Pass ON DATABASE;
-- and please do not forget to enable it when you are done
ENABLE TRIGGER You_Shall_Not_Pass ON DATABASE;

If your developers are total cowboys and cannot be trusted with an option to disable this trigger, you can prevent them from disabling this trigger by denying ALTER permissions to this trigger.

Filed under: CodeProject, Database, Programming