Contained Databases 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.

$Description: C:\Users\dinesha\Desktop\5528EN _03_1stDraft\Images\5528EN _03_01.png$

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:

$Description: C:\Users\dinesha\Desktop\5528EN _03_1stDraft\Images\5528EN _03_02.png$

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

$Description: C:\Users\dinesha\Desktop\5528EN _03_1stDraft\Images\5528EN _03_03.PNG$

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.

Configuring Contained Databases

Leave a comment Cancel reply