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.

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.