Introduction

The Master database. Just in case that you are not familiar with this database, the Master database is used in SQL Server to store all the system information, instance-level settings, logon information, database file locations, etc. SQL Server can't run without access to a fully functional master database. It's highly recommended not to use it to store any user database objects.

This article is going to discuss why user objects end up created in a master database and how to prevent this from happening.

The Problem

The main problem is SSMS' default behavior (to be exact, this is not purely SSMS behavior, but more of a SQL Server login default database setting) . When SSMS user clicks on a New query button, SSMS makes the connection using highlighted/selected server and database (Fig #1). If SSMS doesn't "see" any specific database selected, it will use last server and default database (master most of the time) as a database for that query connection (Fig #2). 

Fig #1

Fig #2

Although this is rarely done, this behavior could be changed by either hard-coding the database name via SSMS connection properties (Fig #3) or by changing the default database per login by running ALTER LOGIN [login] WITH DEFAULT_DATABASE = [some other database than master].

Fig #3

Without specifically selecting a database and keeping defaults as-is, SSMS will use master database. Unless your T-SQL queries are fully declarative (database.schema.object) and/or include  USE [database] reference and/or master database changed in a drop-down (Fig #4), SSMS will run your query in a master database.

Fig #4

It's safe to say that sooner or later, you would create objects by mistake in a master database (Fig #5). Don't worry, it's perfectly human to make mistakes - it happened, its happening and it will keep happening to all of us. Most of the time, you open a new Query window, forget to change the database in a drop-down, create your script, run it, and "pollute" the master database. Since user database objects should normally live in a user database, you would need to relocate those objects.

Fig #5

The Solution

Luckily, it's super easy to prevent user objects from being created in a master database. The following script is going create a DDL trigger in a master database that would prevent creation of any user objects in a that database. Essentially, any attempt to create table/view/function/stored procedure in a master database would be rolled back.

USE master
GO
IF EXISTS(SELECT object_id FROM sys.triggers WHERE name='DDL_BlockMasterUserObjects') DROP TRIGGER DDL_BlockMasterUserObjects ON DATABASE
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Rezhener
-- Create date: 10/21/2020
-- Description: Prevent user objects in a master database
-- =============================================
CREATE TRIGGER DDL_BlockMasterUserObjects
ON DATABASE
FOR CREATE_TABLE, CREATE_VIEW, CREATE_PROCEDURE,CREATE_FUNCTION
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
PRINT 'Creation of user objects in master database is not allowed, please select a different database for your user objects!' ROLLBACK
END
GO

You are all set once you run the script above and see "Commands completed successfully" in the Messages window (Fig #6).

Fig #6

From now and on, any attempt to create an object inside master database will be rejected (see Fig #7). 

Fig #7

Disclaimer

This blog post is partially based on the following resources:

• https://docs.microsoft.com/en-us/sql/relational-databases/databases/master-database?view=sql-server-ver15
• https://www.mssqltips.com/sqlservertip/2131/sql-server-login-issue-with-default-database/