Contained Database in SQL Server 2012

Contained Databases have been an oft requested feature which has finally arrived in SQL Server 2012. In prior SQL Server versions, database were not 100% portable. In case you need to move a database from one server to another using backup and restore, the data in the database will of course be preserved. However, to work with the database, you need several more objects such as logins etc. However, these objects are outside the database and so would not be included in any backup.

With Contained databases, there is only a small functional dependency on SQL Server Instance or in other words, the database is self-contained and all the objects will reside in the database itself.

There are three Contained types.

<><> <><> <><> <><> <><>

Contained Type	Description
NONE	Default contained type is what we are used to have in SQL Server 2008 R2 and prior versions.
PARTIAL	Partially Contained Databases provides some isolation from the instance of SQL Server but not full containment.
FULL	(This is currently not available in SQL Server 2012 but in FULL mode, users will not be not permitted to cross the database)

Configuring Contained Databases

Firstly, you will need to enable the Contained Database feature at the aerver level either from the user interface or from a script.

sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Alternatively you can use the SSMS user interface:
Right click the server, select Properties > Advanced and set Enabled Contained Databases to True.

Next you will need to configure Contained Database at the database level, to do this from using code:

CREATE DATABASE [ContainedDatabase]
CONTAINMENT = PARTIAL
ON PRIMARY
( NAME = N'ContainedDatabase', FILENAME = N'C:\myPath\ContainedDatabase.mdf')
LOG ON
( NAME = N'ContainedDatabase_log', FILENAME = N'C:\myPath\ContainedDatabase_log.ldf')
GO

As you can see in the above script, the CREATE DATABASE syntax is tweaked to enable the contained database feature.
You can use the SSMS user interface to enable contained database at the database level:

In SQL Server 2012, you will see only two options i.e. None and Partial however
we may see the Full contained type in future releases of SQL Server 2012.
The new Contained Databases feature will be reflected in the sys.databases but not in sys.sysdatabases:

SELECT Name,containment
FROM sys.databases

As you can see in the above image, the new column is included to identify whether the database is contained database or not.
You can change the containment to PARTIAL or to NONE by executing following script.

USE [master]
GO
ALTER DATABASE [ContainedDatabase]
SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO
Let us see how user login works with Contained Databases.
If you are creating a user in any of SQL Server 2012 prior versions, this is the syntax you need to use.

USE [master]
GO
CREATE LOGIN [None_Contained_Login]
WITH PASSWORD=N'Qwerty123',
DEFAULT_DATABASE=[master]
GO
USE [AdventureWorks2008R2]
GO
CREATE USER [None_Contained_Login]
FOR LOGIN [None_Contained_Login]
GO
This script will create a login with s password and then create a user by adding the previously created login.
In Contained databases this is how you create a user.

USE [ContainedDatabase]
GO
CREATE USER [Contained_User]
WITH PASSWORD=N'Qwerty123'
GO
Note the two two differences:

• Unlike in the standard (pre SQL Server 2012) user creation, you have only one create statement. In this, you will have only a CREATE USER statement and no CREATE LOGIN statement.
• In SQL server 2012 you assign the password to the USER and not to the LOGIN unlike in the standard user creation.

The above query cannot be run on ‘None’ contained or standard databases. If you attempt to run this statement you will trigger the following error.

Msg 33233, Level 16, State 1, Line 1
You can only create a user with a password in a contained database.

However, you can create a standard user in contained databases since you have used PARTIAL contained database type (Note that in future this will fail in the FULL contained database type).
For UI lovers, right click the users under the Security node in the Contained database and select New User… where you will be taken to the following screen

In the above screen, you will see that the user type is a SQL user with password which is what contained database type requires.

When you login as a contained user, make sure you enter the correct database as the default database. In previous SQL Server versions, logins are unique for the server instance. However, one contained user name can be in another database. Therefore, it is essential to enter database name at the login as shown below.

After login into the server, you will see following in SQL Server Management Studio.

Which means you can only interact with the relevant database and its objects.
After creating contained specific objects like the contained user that we created, you cannot be revert the database back to a ‘None’ contained database. Note the below error which will be encountered if you attempt to create a user using the standard method:

Msg 33233, Level 16, State 1, Line 1
You can only create a user with a password in a contained database.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Apart from users, tables or temporary tables will be created within the database. In previous editions, if your database collation is different from server collation, there will be some issues with temporary tables. The temporary table collation is a server collation therefore joining user tables and temporary tables will result following error:

Cannot resolve the collation
conflict between "SQL_Latin1_General_CP1_CI_AS" and
"Danish_Norwegian_CI_AI" in the equal to operation.

With contained databases, all objects are in the database itself, even temporary tables will be created inside the database. So you won’t have such issues.
There is a new DMV named sys.dm_db_uncontained_entitieswhich shows any uncontained objects in the database. If sys.dm_db_uncontained_entitiesis empty, your database does not use any uncontained entities.

Migrating Users

In Contained Databases you also have the option of converting a non-partially contained database to a partially contained database. However, after converting the database, you need to manually convert users since existing database users are not automatically migrated as well.


The following query will give you existing users in the database.


SELECT
name, type_desc, authentication_type, authentication_type_desc
FROM sys.database_principals

Note that, though the database is changed to Containted, the users are still at INSTANCE level or server level. These users can be converted by following query.


EXECUTE sp_migrate_user_to_contained
@username = N'Dev1',
@rename = N'keep_name',
@disablelogin = N'disable_login'
You can also rename the existing user as well as disable the existing login.
After running the above query, if you execute the query you executed before, you will see that the Dev1 user is now changed to the DATABASE level which is s contained user.