Migrate to a Partially Contained DatabaseMigrate to a Partially Contained Database

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题讨论要更改为部分包含的数据库模型的准备工作,接着提供了迁移步骤。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

在考虑将数据库迁移到部分包含的数据库模型时,请复查以下各项。Review the following items when considering migrating a database to the partially contained database model.

启用包含的数据库Enable Contained Databases

必须先在 SQL Server 数据库引擎SQL Server Database Engine的实例上启用包含的数据库,然后才能创建包含的数据库。Contained databases must be enabled on the instance of SQL Server 数据库引擎SQL Server Database Engine, before contained databases can be created.

使用 Transact-SQL 启用包含的数据库Enabling Contained Databases Using Transact-SQL

下面的示例对 SQL Server 数据库引擎SQL Server Database Engine的实例启用包含的数据库。The following example enables contained databases on the instance of the SQL Server 数据库引擎SQL Server Database Engine.

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

使用 Management Studio 启用包含的数据库Enabling Contained Databases Using Management Studio

下面的示例对 SQL Server 数据库引擎SQL Server Database Engine的实例启用包含的数据库。The following example enables contained databases on the instance of the SQL Server 数据库引擎SQL Server Database Engine.

  1. 在对象资源管理器中,右键单击服务器名称,然后单击“属性” 。In Object Explorer, right-click the server name, and then click Properties.

  2. “高级” 页面上的 “包含” 部分中,将 “启用包含的数据库” 选项设置为 “True”On the Advanced page, in the Containment section, set the Enable Contained Databases option to True.

  3. 单击“确定”。 Click OK.

将数据库转换为部分包含的数据库Converting a Database to Partially Contained

通过更改 CONTAINMENT 选项可以将数据库转换为包含的数据库。A database is converted to a contained database by changing the CONTAINMENT option.

使用 Transact-SQL 将数据库转换为部分包含的数据库Converting a Database to Partially Contained Using Transact-SQL

下面的示例将名为 Accounting 的数据库转换为部分包含的数据库。The following example converts a database named Accounting to a partially contained database.

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

使用 Management Studio 将数据库转换为部分包含的数据库Converting a Database to Partially contained Using Management Studio

下面的示例将数据库转换为部分包含的数据库。The following example converts a database to a partially contained database.

  1. 在对象资源管理器中,展开“数据库” ,右键单击要转换的数据库,然后单击“属性” 。In Object Explorer, expand Databases, right-click the database to be converted, and then click Properties.

  2. “选项” 页面上,将 “包含类型” 选项更改为 “部分”On the Options page, change the Containment type option to Partial.

  3. 单击“确定”。 Click OK.

将用户迁移为包含的数据库用户Migrating Users to Contained Database Users

以下示例将所有基于 SQL ServerSQL Server 登录名的用户迁移到具有密码的包含数据库用户。The following example migrates all users that are based on SQL ServerSQL 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 ;  

另请参阅See Also

包含的数据库 Contained Databases
sp_migrate_user_to_contained (Transact-SQL) sp_migrate_user_to_contained (Transact-SQL)
sys.dm_db_uncontained_entities (Transact-SQL)sys.dm_db_uncontained_entities (Transact-SQL)