SQL Server 2012 Partially Contained Databases Part 1 - Contained Logins

[SQLPFE]

First published on MSDN on Feb 26, 2013

The concept of database containment basically means that all instance level dependencies are severed from the database.  If you have ever implemented log shipping or mirroring, you are probably aware of many of these.  Instance level logins, linked servers, SQL Agent jobs, SSIS packages, Tempdb collation, and even other databases often need to be manually copied and synchronized between instances when a database is being log shipped, mirrored, or part of an Availability Group.

The Database containment feature puts all of these items within the database itself.  This way when you copy a database from one instance to another, you can be sure you moved everything.  The end goal is to fully separate database management from application functions.

For SQL 2012, Partial Containment was implemented to resolve two of the most common dependencies, logins and collation.  This must be enabled at the instance level first, so that database owners cannot simply enable containment without the knowledge of the database administrator. It is a simple sp_configure command as follows:

EXEC sp_configure 'contained database authentication', 1

Once contained database authentication is enabled, you can then set the Containment Type to partial in the database options tab in Management Studio.

It can also be done in an ALTER or CREATE DATABASE statement as follows:

ALTER DATABASE <name> SET CONTAINMENT = PARTIAL

Contained Logins:

Now that you have a partially contained database, you can create contained users that authenticate within the database. For SQL authentication, the authentication is at the database level instead of the instance level. The user is not mapped to a login and the password hash is stored within the user database,not master.  For users logging in to contained databases, authentication is first tried at the database level and then at the instance level if there is no contained user with that name. On the other hand, Windows users look relatively similar to before, but they have no matching login.  Authentication for Windows users tries at the instance level first, and then at the user level within the database.  You need to consider the order of authentication when you will have contained and non-contained usage of a user in multiple databases on an instance.  Here you can see what a contained user looks like in Management Studio:

There are some other considerations to take into account when using contained logins.  Setting AUTO CLOSE on will not allow the contained users to connect when there are no other connections tot he database as the database will be closed.  This can cause a denial of service type effect, so it is definitely recommended not to use AUTO CLOSE. Also, granting the ALTER ANY USER privilege at the database level allows users to be added. Since typically a login would need to be added first, it is not considered a huge security concern.  When the database is contained, then it is the equivalent of adding a new login, so in this case it is more of a security concern. Note that you can use the sp_migrate_user_to_contained stored procedure to migrate traditional database users to contained database users.

My next post we will explore contained databases and collation.  In the meantime, use the following script to enable containment, create databases, and create/migrate users. This will help you explore partial database containment on a test system.

--enable contained dbs
EXEC sys.sp_configure N'contained database authentication'
	,1
GO

RECONFIGURE
GO

--Create the 3 databases, all have different collation from the instance collation
--- Some of these will be used in the subsequent post.
CREATE DATABASE [MyDB] --not contained     
	COLLATE Latin1_General_CI_AS_KS_WS
GO

CREATE DATABASE [MyContainedDB] --partially contained     
	CONTAINMENT = PARTIAL COLLATE Latin1_General_CI_AS_KS_WS
GO

CREATE DATABASE [MyContainedDBToo] --partially contained to illustrate multiple collations     
	CONTAINMENT = PARTIAL COLLATE Latin1_General_CS_AS_KS
GO

--Create a non-contained Login mapped to a user
USE [master]
GO

CREATE LOGIN [TestSQLAccount]
	WITH PASSWORD = N'iL0V3SQL!'
		,DEFAULT_DATABASE = [MyContainedDB]
GO

USE [MyContainedDB]
GO

CREATE USER [TestSQLAccount]
FOR LOGIN [TestSQLAccount]
GO

--View  the TestSQLAccount User in Management studio under the MyContainedDB to see login affiliation
--Convert this to a contained user    
USE [MyContainedDB]
GO

EXECUTE sp_migrate_user_to_contained @username = N'TestSQLAccount'
	,@rename = N'keep_name'
	,@disablelogin = N'disable_login';
GO

--Look in Management studio under general tab - you no longer see login affiliation and you see password info    
--If you want to log in as the contained user, you must specify the database name in the connection string.You can use the sample cmd below to log in as the contained user for testing purposes    
--sqlcmd -S <put your instance name here> -U TestSQLAccount -P iL0V3SQL! -d MyContainedDB    
-- Create a contained SQL user without login    
USE [MyContainedDB]
GO

CREATE USER MyContainedUser
	WITH PASSWORD = 'iL0V3SQL!';
GO

Note: Here are some Limitations of partially contained databases:

Lisa Gardner - SQL Premier Field Engineer