SQL Server 2012 Partially Contained Databases Part 1 - Contained Logins

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:

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