sp_migrate_user_to_contained (Transact-SQL)sp_migrate_user_to_contained (Transact-SQL)

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

将映射到 SQL ServerSQL Server 登录名的数据库用户转换为具有密码的包含数据库用户。Converts a database user that is mapped to a SQL ServerSQL Server login, to a contained database user with password. 在包含的数据库中,请使用此过程删除安装了该数据库的 SQL ServerSQL Server 实例的依赖项。In a contained database, use this procedure to remove dependencies on the instance of SQL ServerSQL Server where the database is installed. sp_migrate_user_to_contained将用户与原始SQL ServerSQL Server登录名,以便可以为包含的数据库单独管理密码和默认语言等设置。sp_migrate_user_to_contained separates the user from the original SQL ServerSQL Server login, so that settings such as password and default language can be administered separately for the contained database. sp_migrate_user_to_contained将包含的数据库移到另一个实例之前,可以使用SQL Server 数据库引擎SQL Server Database Engine以取消对当前的依赖关系SQL ServerSQL Server实例登录名。sp_migrate_user_to_contained can be used before moving the contained database to a different instance of the SQL Server 数据库引擎SQL Server Database Engine to eliminate dependencies on the current SQL ServerSQL Server instance logins.

备注

使用时要小心sp_migrate_user_to_contained,如您将不能反向效果。Be careful when using sp_migrate_user_to_contained, as you will not be able to reverse the effect. 仅在包含数据库中使用此过程。This procedure is only used in a contained database. 有关详细信息,请参阅 Contained DatabasesFor more information, see Contained Databases.

语法Syntax

  
sp_migrate_user_to_contained [ @username = ] N'user' ,   
    [ @rename = ] { N'copy_login_name' | N'keep_name' } ,   
    [ @disablelogin = ] { N'disable_login' | N'do_not_disable_login' }   

参数Arguments

[ @username = ] N'user'[@username = ] N'user'
当前包含的数据库中的用户名称,该用户将映射到经过身份验证的 SQL ServerSQL Server 登录名。Name of a user in the current contained database that is mapped to a SQL ServerSQL Server authenticated login. 值是sysname,默认值为NULLThe value is sysname, with a default of NULL.

[ @rename = ] N'copy_login_name' | N'keep_name'[@rename = ] N'copy_login_name' | N'keep_name'
如果基于登录名的数据库用户具有不同的用户名与登录名,使用keep_name在迁移期间保留的数据库用户名称。When a database user based on a login has a different user name than the login name, use keep_name to retain the database user name during the migration. 使用copy_login_name若要创建新的包含的数据库用户的登录名,而不是用户的名称。Use copy_login_name to create the new contained database user with the name of the login, instead of the user. 如果基于登录名的数据库用户具有与登录名相同的用户名,这两个选项将创建包含数据库用户而不更改名称。When a database user based on a login has the same user name as the login name, both options create the contained database user without changing the name.

[ @disablelogin = ] N'disable_login' | N'do_not_disable_login'[@disablelogin = ] N'disable_login' | N'do_not_disable_login'
disable_login禁用登录名的 master 数据库中。disable_login disables the login in the master database. 若要连接该登录名处于禁用状态时,该连接必须提供包含的数据库名称作为初始目录作为连接字符串的一部分。To connect when the login is disabled, the connection must provide the contained database name as the initial catalog as part of the connection string.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

sp_migrate_user_to_contained创建包含的数据库用户具有密码,而不考虑属性或登录名的权限。sp_migrate_user_to_contained creates the contained database user with password, regardless of the properties or permissions of the login. 例如,过程可能会成功如果禁用该登录名或用户被拒绝CONNECT对数据库的权限。For example, the procedure can succeed if the login is disabled or if the user is denied the CONNECT permission to the database.

sp_migrate_user_to_contained具有以下限制。sp_migrate_user_to_contained has the following restrictions.

  • 数据库中不能已存在此用户名。The user name cannot already exist in the database.

  • 无法转换内置用户,例如,dbo 和 guest。Built-in users, for example dbo and guest, cannot be converted.

  • 不能中指定的用户EXECUTE AS子句的已签名的存储过程。The user cannot be specified in the EXECUTE AS clause of a signed stored procedure.

  • 用户不能拥有一个存储的过程中包括EXECUTE AS OWNER子句。The user cannot own a stored procedure that includes the EXECUTE AS OWNER clause.

  • sp_migrate_user_to_contained不能在系统数据库中使用。sp_migrate_user_to_contained cannot be used in a system database.

安全性Security

在迁移用户时,切勿从 SQL ServerSQL Server 实例中禁用或删除所有管理员登录名。When migrating users, be careful not to disable or delete all the administrator logins from the instance of SQL ServerSQL Server. 如果删除所有登录名,请参阅连接到 SQL Server 系统管理员被锁定时If all logins are deleted, see Connect to SQL Server When System Administrators Are Locked Out.

如果BUILTIN\Administrators登录名存在,管理员可以通过启动其应用程序使用连接以管理员身份运行选项。If the BUILTIN\Administrators login is present, administrators can connect by starting their application using the Run as Administrator option.

权限Permissions

要求具有 CONTROL SERVER 权限。Requires the CONTROL SERVER permission.

示例Examples

A.A. 迁移单个用户Migrating a single user

以下示例将名为 BarrySQL ServerSQL Server 登录名迁移到具有密码的包含数据库用户。The following example migrates a SQL ServerSQL Server login named Barry, to a contained database user with password. 该示例不会更改用户名称,并将该登录名保留为已启用。The example does not change the user name, and retains the login as enabled.

sp_migrate_user_to_contained   
@username = N'Barry',  
@rename = N'keep_name',  
@disablelogin = N'do_not_disable_login' ;  
  

B.B. 将所有具有登录名的数据库用户迁移到没有登录名的包含数据库用户Migrating all database users with logins to contained database users without logins

以下示例将所有基于 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

Migrate to a Partially Contained Database Migrate to a Partially Contained Database
包含的数据库Contained Databases