Migrate to a Partially Contained Database

Review the following items when considering migrating a database to the partially contained database model.

• You should understand the partially contained database model. For more information, see Contained Databases.

• You should understand risks that are unique to partially contained databases. For more information, see Security Best Practices with Contained Databases.

• Contained databases do not support replication, change data capture, or change tracking. Confirm the database does not use these features.

• Review the list of database features that are modified for partially contained databases. For more information, see Modified Features (Contained Database).

• Query sys.dm_db_uncontained_entities (Transact-SQL) to find uncontained objects or features in the database. For more information, see.

• Monitor the database_uncontained_usage XEvent to see when uncontained features are used.

Contained databases must be enabled on the instance of SQL Server Database Engine, before contained databases can be created.

Enabling Contained Databases Using Transact-SQL

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

A database is converted to a contained database by changing the CONTAINMENT option.

Converting a Database to Partially Contained Using Transact-SQL

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

Converting a Database to Partially contained Using Management Studio

The following example migrates all users that are based on SQL Server logins to contained database users with passwords. The example excludes logins that are not enabled. The example must be executed in 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 ;

Reference

Concepts