SQL Server 2012 supports contained databases and partially contained databases, which provide a high degree of isolation from the database engine instance but are not fully contained. The contained database becomes highly portable that you can migrate it to any instance or even in SQL Azure.
In SQL Server 2012, the implementation of partially contained database does not support the following
- Change Tracking
- Change Data Capture
- Binding changes resulting from collation changes
You can enable contained databases at the server level by opening the server properties in SSMS or by running the following statements
–Turn on contained database at server level
sp_configure ‘show advanced’, 1
sp_configure ‘contained database authentication’, 1
Then after everything is OK, proceed to the database which you wish to enable containment. Right click and open the database properties. You can also use the ALTER DATABASE statement with the SET CONTAINMENT option to configure.
Now to test and play around with the database containment feature, run the following scripts below;
–Create containted database
CREATE DATABASE [BlogDemoDBContained]
CONTAINMENT = PARTIAL
–Create SQL Login contained user in database
CREATE USER democontaineduser WITH PASSWORD=N’Passw0rd’, DEFAULT_SCHEMA=[dbo]
Then disconnect your current query window. Try to connect to database via the GUI and but click on OPTIONS to open window same as below;
Make sure to select the contained database in the Connect to database drop down list. Enter the credentials of your demo contained user we created above and viola you should be able to connect to the instance showing only the contained database. There you go.