Contained Database

Contained Database is a new feature of SQL Server 2012.

As per MS Contained Database is defined as:

“A Contained Database is database that is isolated from other databases and from the instance of SQL Server that hosts the Database. ”

In Simple words:

Now Database can store the User information/Credentials in Database it self instead of Instance level.

This feature is very useful when moving the database from one server to another server.

The containment setting of a database can be NONE,PARTIAL or FULL. 

SQL Server 2012 supports only NONE and PARTIAL Containment settings.

Requirements of Contained Databases:

1. Contained Databases need to enabled on instance
2. Contained Database need to be specified while connecting thru SSMS.

First we need to enable the contained databases at instance level. That we can do it in two ways.

USE master

GO

sp_configure 'show   advanced options', 1

GO

RECONFIGURE

GO

sp_configure 'CONTAINED   DATABASE AUTHENTICATION', 1

GO

RECONFIGURE

GO

sp_configure 'show   advanced options', 0

GO

RECONFIGURE

GO

Second way is to do it from SSMS.

From ssms right click on sqlserver instance then Click Adavnced Page then set the Enable Contained Database property to TRUE.

Once its enabled at instance level, we can create the database by stating Containment

When creating a database Database from SSMS, we need to mention the containment type property value to “Partial”  as shown in below image .

Once Database is created with Partial containment we need to create users in it. These users authenticated by Database rather than Instance.

USE [Contained_Demo]

GO

CREATE USER   [User_Demo] WITH   PASSWORD=N'demo1234',

DEFAULT_SCHEMA=[dbo]

GO

Now add the user to db_owner role

USE [Contained_Demo]

GO

ALTER ROLE   [db_owner] ADD   MEMBER [User_Demo]

let’s try to connect to the database using the user account created above, which is expected to be authenticated by the database:

Ohh..its throws an error. As mentioned in the above we need to mention the Database name in connection string.

In connection properties we need to mention the Database name 

When sucessfully authenticated by DB, User will see only DB to which user has access..

Advantages of Contained Database:

1. Easy to move database from one server to another.
2. Authentication is done by DB rather than Instance
3. contained Database users can be windows/Sql authenticated.

Limitations:

1. Partially Contained databases cannot use CDC,CT,Replication.