Migrate to a Partially Contained Database

Article
02/28/2023

Applies to: SQL Server

This topic discusses how to prepare to change to the partially contained database model and then provides the migration steps.

In this topic:

Preparing to Migrate a Database
Enable Contained Databases
Converting a Database to Partially Contained
Migrating Users to Contained Database Users

Preparing to Migrate a 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.

Enable Contained Databases

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

The following example enables contained databases on the instance of the SQL Server Database Engine.

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

Enabling Contained Databases Using Management Studio

The following example enables contained databases on the instance of the SQL Server Database Engine.

In Object Explorer, right-click the server name, and then click Properties.
On the Advanced page, in the Containment section, set the Enable Contained Databases option to True.
Select OK.

Converting a Database to Partially Contained

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

Converting a Database to Partially Contained Using Transact-SQL

The following example converts a database named Accounting to a partially contained database.

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

Converting a Database to Partially contained Using Management Studio

The following example converts a database to a partially contained database.

In Object Explorer, expand Databases, right-click the database to be converted, and then click Properties.
On the Options page, change the Containment type option to Partial.
Select OK.

Migrating Users to Contained Database Users

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 ;

Share via