Using Contained Databases in SQL Server, Part 2

Contained databases were first introduced in SQL Server® 2012. They make it easier to deploy databases to different SQL Server instances by removing the requirement to separately transfer server logins and permissions apart from the database that you want to deploy.

In Using Contained Databases Part 1, I provided an overview of contained databases. I then stepped through the processes of enabling a contained database, creating a new contained database, and creating contained database users. In this article, I’ll pick up where that article left off and show you how you can deploy a contained database to a new server, as well as how to migrate an existing non-contained database to a contained database.

Migrating Existing Databases to Contained Databases

To migrate an existing non-contained database to a contained database you first need to make sure that the SQL Server instance supports contained databases. This was shown in Part 1, but because it’s a requirement, and it’s not on by default, I’ll repeat it here. To enable contained databases you need to use the SP_CONFIGURE command with the ‘contained database authentication’ option enabled as you can see in the following listing.

SP_CONFIGURE 'contained database authentication', 1;
GO
RECONFIGURE;
GO

Next, you can convert a non-contained database to a contained database by using the ALTER DATABASE command to change the CONTAINMENT option to PARTIAL like you can see in the listing below.

USE [master]
GO
ALTER DATABASE [EditorialDB] SET CONTAINMENT = PARTIAL
GO

The ALTER DATABASE command with the CONTAINMENT=PARTIAL option will convert the database. To find any uncontained objects or features in the database you can query the sys.dm_db_uncontained_entities view. You can find more information at sys.dm_db_uncontained_entities. You can also monitor the database_uncontained_usage XEvent to see when any uncontained database features are used.

After the database itself has been converted to a contained database, you still need to convert the server’s logins to contained database users. You can use the sp_migrate_user_to_contained stored procedure to accomplish that as you can see in the listing below.

USE [EditorialDB]
GO
sp_migrate_user_to_contained
    @username = N'MTEditorialDBUser',
    @rename = N'keep_name',
    @disablelogin = N'do_not_disable_login' ;
GO

If you want to migrate users in bulk, you might want to run the following example script from the Microsoft® TechNet site, which migrates all users that are based on SQL Server logins to contained database users with passwords. This script must be executed from the contained database.

DECLARE @username sysname ;
DECLARE user_cursor CURSOR
    FOR 
        SELECT dp.name 
        FROM sys.database_principals AS dp
        JOIN sys.server_principals AS sp 
        ON dp.sid = sp.sid
        WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXECUTE sp_migrate_user_to_contained 
        @username = @username,
        @rename = N'keep_name',
        @disablelogin = N'disable_login';
    FETCH NEXT FROM user_cursor INTO @username
    END
CLOSE user_cursor ;
DEALLOCATE user_cursor ;

Deploying Contained Databases

Deploying contained databases is as easy as backing up the database and restoring it on the target system. Alternatively, you can detach the database, move the data and log files, and then reattach them to your target server. After the database is brought online on the target server, the contained database users can log in to the database and use the different objects in the contained database—no need to migrate SQL Server logins.

For more information about migrating to a contained database, and the sample script, you can check out Migrate to a Partially Contained Database on Microsoft TechNet.