Denali – Day 7 : Contained Database

Contained database is another new feature for sql server 2012 – Denali, earlier version has a big issue on logins/users associated with particular database when you want to move the database from one server to another there is a big issue of login/users we have to manually sync the logins and check for orphan users.

Now using contained database when you configure the database as contained the logins /users will be with database and it moves automatically.

To make the database “contained” using management studio as follows:

Using T-Sql

sp_configure ‘show advanced options’, 1

GO

RECONFIGURE WITH OVERRIDE

GO

sp_configure ‘contained database authentication’, 1

GO

RECONFIGURE WITH OVERRIDE

GO

You have to enable the option at the server level by default its not. Once you enable it at server level then when you check the property of the database you will have contained option.

There are two options on it “None” and “Partial”

CREATE DATABASE <DB_CONTAIN1>

CONTAINMENT = PARTIAL

The good thing about it is it keeps the user/login information on that specific database only not only in “Master”