How to Configure a Contained Database Feature in SQL Server

Contained Databases is a new feature which was initially introduced in SQL Server 2012. A contained database is a database that will store all its metadata within the database thereby not storing any configuration information within the master database of the SQL Server Instance where the Contained Database is created. A contained database is isolated from other databases which are available on the instance of SQL Server. In this article we will take a look at how to configure and implement Contained Database feature of SQL Server 2012.

This article is divided into four parts for easier understanding of the concept for DBA’s and Developers.

• How to Configure a Contained Database Feature in SQL Server
• How to Create a Contained Database in SQL Server 2012 and Higher Versions
• How to Create a SQL Server or Windows Login which will have access to the Contained Database
• How to Connect to Contained Database in SQL Server

How to Configure a Contained Database Feature in SQL Server

Different ways to configure Contained Database feature in SQL Server 2012 are:-

• Using SQL Server Management Studio
• Using a T-SQL script

How to Configure Contained Database Feature in SQL Server Using SSMS

Step 1. Connect to SQL Server 2012 Instance using SQL Server Management Studio and then right click the SQL Server Instance and choose Properties from the drop down list.

Step 2. In Server Properties choose Advanced Page and set the value as True for Enable Contained Databases as highlighted in the below snippet and then click OK to save the changes.

How to Configure Contained Database Feature in SQL Server Using T-SQL Code

Execute the below T-SQL to configure contained database feature in SQL Server at instance level.

USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE
GO

Once the above TSQL Script is executed successfully the Contained Database feature will be enabled at the SQL Server Instance level.

Click the below Number Buttons to continue reading the rest of the article.