For example consider the following scenario: For audit purposes, you want to set your SQL Server instance, each time a user database is created, to automatically create an audit-related table as well as a relevant stored procedure that will allow each application that uses a database to be able to store in a table (in each database) the log-in and log-out times for each user.
To do this, we can create the relevant table and stored procedure in the Model database:
CREATE TABLE [dbo].[AuditLog](
[userID] [int] NOT NULL,
[loginTime] [datetime] NOT NULL,
[logoutTime] [datetime] NOT NULL
) ON [PRIMARY]
CREATE PROCEDURE dbo.logUser
INSERT INTO dbo.AuditLog
VALUES (@userID, @loginTime, @logoutTime)
Let’s take a look at the Model database:
|Figure 1: The Model database after adding the table and stored procedure.|
OK, so we have added the table and stored procedure in the Model database. Now let’s create three empty sample databases and check if the same tables and stored procedures are created automatically upon their creation:
CREATE DATABASE [SampleDB1];
CREATE DATABASE [SampleDB2];
CREATE DATABASE [SampleDB3];
|Figure 2: SampleDB1|
|Figure 3: SampleDB2|
|Figure 4: SampleDB3|
As you can see, all three databases were created fully based on the (modified) Model system database, thus the “AuditLog” table and “logUser” stored procedure were automatically created.
You can use the Model database for other things as well, as the above was just a simple example. However always have in mind when creating objects in the Model database that they will be automatically created for all user databases that will be created on that instance so be careful!