System Databases : Restrictions Matrix


Dear Readers,

Today, I tried to consolidate a list of restrictions that are applied to system databases. Restrictions are nothing but the operations that you “can not” perform on a system database.

Below is a consolidated matrix (rows are restrictions) and when you see YES in the column, that means restriction is applied. If there is nothing, that means no restriction for that database.

I will try to post another blog on this soon. I hope this helps for now..

Additionally, you may try reading our blog “10 Interesting things that ‘CAN NOT’ be done in SQL Server

Restriction Master MSDB Model Tempdb
Adding files Yes Yes
Adding Filegroups Yes Yes Yes
Changing collation Yes Yes Yes Yes
Changing the database owner Yes Yes Yes
Creating a full-text catalog or full-text index Yes
Creating triggers on system tables in the database Yes
Dropping the database Yes Yes Yes Yes
Dropping the guest user from the database. Yes Yes Yes Yes
Enabling change data capture. Yes Yes Yes Yes
Participating in database mirroring. Yes Yes Yes Yes
Removing the primary filegroup Yes Yes Yes Yes
Removing the primary data file Yes Yes Yes Yes
Removing the log file. Yes Yes Yes Yes
Renaming primary filegroup. Yes Yes Yes Yes
Setting the database to OFFLINE Yes Yes Yes Yes
Setting the database to READ_ONLY Yes Yes
Setting the primary filegroup to READ_ONLY. Yes Yes Yes Yes
Creating procedures, views, or triggers
using the WITH ENCRYPTION option
Yes
Backing up or restoring the database. Yes
Creating a database snapshot. Yes
Running DBCC CHECKALLOC. Yes
Running DBCC CHECKCATALOG. Yes
Renaming the Database Yes Yes Yes Yes