What is the purpose?
These database are easy to port from instance / server to instance / server. You don't have to worry about the user logins associated with the database. This is very handy when you are dealing with large number of database users.
Any Important Note / drawback?
Important note is that in SQLServer 2012, only Partial containment type is supported. It means that your database can be a None contained database or partially contained database. A partially contained database is a contained database that can allow some features that cross the database boundary. SQL Server includes the ability to determine when the containment boundary is crossed. Fully contained user entities (those that never cross the database boundary), for example sys.indexes. Any code that uses these features or any object that references only these entities is also fully contained.
As stated above, SQL Server 2012 is supported with only partially contained state. A partially contained database is a contained database that allows the use of uncontained features.
According to BOL, use the sys.dm_db_uncontained_entities and sys.sql_modules view to return information about uncontained objects or features. By determining the containment status of the elements of your database, you can discover what objects or features must be replaced or altered to promote containment.
The behavior of partially contained databases differs most distinctly from that of non-contained databases with regard to collation.
How to create?
Ok, enough theory. there are three steps to create a contained database;
1. First, we need to enable the "contained database authentication". We can do this using T-SQL;
sp_configure 'contained database authentication', 1;
CREATE DATABASE TestDBCONTAINMENT=PARTIAL
--Create table with recordsUSE TestDB
CREATE TABLE tblTest(id int ,
Name varchar (250)
INSERT INTO tblTest
CREATE USER TestUser WITH PASSWORD=N'testCUser1$',DEFAULT_SCHEMA=dbo
EXEC sp_addrolemember'db_owner', 'TestUser'
Next, we have to specify the name of the contained database. You will have to type it as the user you have specified does not exists in the sql server instance.
Ok, what about the old databases that i need to convert to partial contained database?
We can convert an uncontained database to partial contained database by using the following 3 steps;
1. Enable the "contained database authentication" as above.
2. Alter the database as;
ALTER DATABASE [MyDatabase]
@username = N'MyDBLogin',
@rename = N'keep_name',
@disablelogin = N'disable_login'
In the above statement, we have un-authorized the 'MyDBLogin' to log in the instance. We did not changed the login name here by specifying the value 'keep_name' for @rename.
Now, if you log in using this user as in our last example, you will only see the 'MyDatabase' in teh object explorer.
Hope this will help you.