Model database in sql server

As we know model database acts as a template for other new databases which are gets created on sql server. That means if I do any changes in model database that will reflect in the new database of sql server.

 Lets go through with small example to understand this feature better.

 In SQL Server 2005, we have changed model database property to read only mode, now we can see model database in grey color with read only permission.  To check whether that will reflect in the new database or not, we will create a new database in sql server with name testdb by using Sql server management studio (SSMS).

 After creating a database in sql server, we can see database in grey color with ready only access to the users. Here “testdb” has inherited the features of model or we can say that master database taken model database as a base template for creation of new database in sql server using SSMS.

 You can observe this behavior, if you are creating database using SQL server management Studio (SSMS). If you are using Transact- SQL to create new database then you won’t see this behavior. You will have database with all permissions.

 Create database testdb

 Note: Ideally we should use T-SQL to create database.

Leave a Reply

  

  

  


1 + 9 =