Contained Databases

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.

Benefits of using Partially Contained Databases

---

There are issues and complications associated with the non-contained databases that can be resolved by using a partially contained database.

Database Movement

when you move database from one instance to another instance, If a login/users are at instance level not at database level and this object is left and it is time consuming to find the user.

The partially contained database can store important information in the database so the database still has the information after it is moved.

to enable the option run the following script in SSMS 2012

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

when you create a database add option SET CONTAINMENT = PARTIAL.

EX: ALTER DATABASE MYDB1 SET CONTAINMENT = PARTIAL GO