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

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

将映射到 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 '用户'[@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_containedsp_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 ServerIf 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