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:
CREATE TRIGGER You_Shall_Not_Pass
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_TABLE, ALTER_TABLE, CREATE_TABLE,
DROP_TRIGGER, ALTER_TRIGGER, CREATE_TRIGGER,
DROP_VIEW, ALTER_VIEW, CREATE_VIEW,
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.'
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;
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