Denali — Day 7: Contained Database

 

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”

About these ads
This entry was posted in Backup and Restore, Denali, Whats New and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s