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 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).
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].
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.
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.
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).
From now and on, any attempt to create an object inside master database will be rejected (see Fig #7).
This blog post is partially based on the following resources: