Contained Database

Contained database:

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.

Contained databases have no dependencies on server-level metadata and settings. These databases are fully portable, which makes it easier for a DBA to move databases between instances without having to deal with issues like orphaned users.

Contained Database Authentication is not enabled by default on the SQL Server instance. First you need to enable it at the instance level before you can create a database with containment or a database that can authenticate users.

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.

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.

Database Containment

The database itself also has a containment setting. In this case, you can look at the properties for the database, on the options tab. You have “None” and “Partial” available, and clicking Partial will enable containment in 2012.

How to Configure Database Containment Feature in SQL Server Using TSQL

Step 1.
The code will appear as below:

USE [master]
GO
ALTER DATABASE [ContainedDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

You can also set this value when you create a database:

-- Create db with containment
CREATE DATABASE ContainedDBCONTAINMENT = PARTIAL

That’s it.

How to Configure Database Containment Feature in SQL Server Using SSMS

These two settings together will enable containment on a database, and then you can work with contained users.
–Create a contained database user:
CREATE USER [Anurag]
WITH PASSWORD=‘clover@123’,
DEFAULT_SCHEMA=[dbo]
GO
–Give the newly created user permissions to access the database:
EXEC sp_addrolemember ‘db_owner’, ‘Anurag’
GO

Connect with created orphaned user & specify database name ContainedDB in Connection Properties: