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 |