http://www.sqlservercentral.com/blogs/vinaythakur/2012/05/07/denali-day-7-contained-database/

Printed 2014/09/21 04:56AM

Denali — Day 7: Contained Database

2012/05/07

 

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”



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.