model Database

When SQL Server is installed the setup program creates the MASTER database device. One of the databases it creates and places on that device is model. This database provides the template or prototype on which new user databases are based. Each time a database is created, SQL Server makes a copy of the model database and then extends it to the creation size requested.

Important A new database can never be smaller than the model database.

The model database contains the system tables required for each user database. It can be modified to customize the structure of newly created databases so that every change you make to model is reflected in each new database. These are some of the changes commonly made to model:

• User datatypes, constraints, rules, or defaults are added.
• Users who are to be given access to all databases on a SQL Server are added.
• Default privileges, particularly for guest accounts, are established in model.
• Database configuration options (such as select into/bulkcopy) are set in the model database. The setting is reflected in all new databases.

Typically, most users are not granted permission to modify the model database. There is no need to grant read permission either, since the entire contents of model are copied into each new user database.