Contained Database

What is it?

A contained database is a database within a sql server instance that is not dependent upon the instance itself in terms of users and metadata. All users and metadata is stored within the contained database.

What is the purpose?

These database are easy to port from instance / server to instance / server. You don't have to worry about the user logins associated with the database. This is very handy when you are dealing with large number of database users.

Any Important Note / drawback?

Important note is that in SQLServer 2012, only Partial containment type is supported. It means that your database can be a None contained database or partially contained database. A partially contained database is a contained database that can allow some features that cross the database boundary. SQL Server includes the ability to determine when the containment boundary is crossed. Fully contained user entities (those that never cross the database boundary), for example sys.indexes. Any code that uses these features or any object that references only these entities is also fully contained.

As stated above, SQL Server 2012 is supported with only partially contained state. A partially contained database is a contained database that allows the use of uncontained features.

According to BOL, use the sys.dm_db_uncontained_entities and sys.sql_modules view to return information about uncontained objects or features. By determining the containment status of the elements of your database, you can discover what objects or features must be replaced or altered to promote containment.

The behavior of partially contained databases differs most distinctly from that of non-contained databases with regard to collation. 

How to create?

Ok, enough theory. there are three steps to create a contained database;

1. First, we need to enable the "contained database authentication". We can do this using T-SQL;

sp_configure 'contained database authentication', 1;

GO

RECONFIGURE

GO

2. Then we create a new database as contained database;

USE master

GO

CREATE  DATABASE TestDB

CONTAINMENT=PARTIAL
GO

--Create table with records

USE TestDB
GO

CREATE TABLE tblTest(

id int ,
Name varchar (250)
)

GO

INSERT INTO tblTest

VALUES

(10,'Atif'),
(20,'Imran'),
(30,'Asif')
GO 

3. Then, at last, we will be creating the user(s) for the our partially contained database;

USE TestDB

GO

CREATE USER TestUser WITH PASSWORD=N'testCUser1$',DEFAULT_SCHEMA=dbo

GO

EXEC sp_addrolemember'db_owner', 'TestUser'

GO

In order to test our exercise, we have to log into a new session using the above created user. Check you server Server authentication settings. As I am using sql server user and not a Domain user, the instance should be configured as "SQL Server and Windows authentication mode". In the figure I have specified the user name and password of the user that we have created in the contained database;

Next, we have to specify the name of the contained database. You will have to type it as the user you have specified does not exists in the sql server instance.

Once the login name is authenticated, you will have you object explorer listing only the contained database;

That's it. We have successfully created a contained database.
Ok, what about the old databases that i need to convert to partial contained database?

We can convert an uncontained database to partial contained database by using the following 3 steps;

1. Enable the "contained database authentication" as above.
2. Alter the database as;

ALTER DATABASE [MyDatabase]

SET CONTAINMENT=PARTIAL

GO

3. In the last step, we move the user(s) to the database;

sp_migrate_user_to_contained

@username = N'MyDBLogin',

@rename = N'keep_name',

@disablelogin = N'disable_login'

GO

In the above statement, we have un-authorized the 'MyDBLogin' to log in the instance. We did not changed the login name here by specifying the value 'keep_name' for @rename.

Now, if you log in using this user as in our last example, you will only see the 'MyDatabase' in teh object explorer.

Hope this will help you.