在 SQL Server 实例之间传输登录名和密码

原始产品版本：SQL Server
原始 KB 数： 918992、246133

概要

本文介绍如何在 Windows 上运行 的 Microsoft SQL Server 的不同实例之间传输登录名和密码。 在服务器之间迁移数据库或需要跨 SQL Server 实例保持一致的用户访问时，请使用这些过程。 这些实例可以位于同一服务器或不同的服务器上，其版本可能有所不同。

为什么在 SQL Server 实例之间传输登录名？

将数据库移动到新服务器（例如在迁移或还原期间），移动数据库用户，但其相应的服务器级登录名可能不存在于新实例上。 此条件创建 孤立用户。 传输登录名和密码对于保持安全性和访问连续性至关重要。

将数据库从服务器 A 上的 SQL Server 实例移动到服务器 B 上的 SQL Server 实例后，用户可能无法登录到服务器 B 上的数据库服务器。此外，用户可能会收到以下错误消息：

用户“MyUser”登录失败。 （Microsoft SQL Server，错误：18456）

出现此问题的原因是服务器 A 上的 SQL Server 实例中的登录名在服务器 B 上的 SQL Server 实例中不存在。

错误 18456 也可能因为其他几个原因而发生。 有关各种原因及其解决方法的详细信息，请参阅 MSSQLSERVER_18456。

在 SQL Server 实例之间传输登录名的步骤

若要传输登录名，请根据需要使用以下方法之一。

通过源服务器上的 SSMS 生成脚本，并为目标服务器上的 SQL Server 登录名手动重置密码

可以使用数据库的“生成脚本”选项在 SQL Server Management Studio （SSMS）中生成登录脚本。

若要通过源服务器上的 SSMS 生成脚本，并为目标服务器上的 SQL Server 登录名手动重置密码，请执行以下步骤：

1. 连接到托管源 SQL Server 的服务器 A。

2. 展开 “数据库” 节点。

3. 右键单击任何用户数据库，然后选择“任务>生成脚本”。

4. “简介”页随即打开 。 选择“下一步”以打开“选择对象”页。 选择“编写整个数据库及所有数据库对象的脚本” 。

5. 选择“下一步”以打开“设置脚本编写选项”页面 。

6. 为 脚本登录选项选择“高级 ”按钮。

7. 在 “高级 ”列表中，找到 脚本登录名，将选项设置为 True ，然后选择“ 确定”。

8. 返回到 “设置脚本选项”，在 “选择脚本保存方式 ”下 ，选择“在新查询窗口中打开”。

9. 选择“ 下一步 ”两次，然后选择“ 完成”。

10. 在包含登录名的脚本中查找部分。 通常，生成的脚本包含文本，本节开头包含以下注释：

    /* For security reasons the login is created disabled and with a random password. */

    备注

    此注释指示 SQL Server 身份验证登录名是使用随机密码生成的，默认情况下处于禁用状态。 必须重置密码并在目标服务器上重新启用这些登录名。

11. 将较大生成的脚本中的登录脚本应用到目标 SQL Server。

12. 对于任何 SQL Server 身份验证登录名，请重置目标 SQL Server 上的密码并重新启用这些登录名。

使用源服务器上生成的脚本（服务器 A）将登录名和密码传输到目标服务器（服务器 B）

1. 创建存储过程，帮助生成必要的脚本来传输登录名及其密码。 为此，请使用 SQL Server Management Studio（SSMS） 或任何其他客户端工具连接到服务器 A 并运行以下脚本：

   USE [master]
   GO
   IF OBJECT_ID('dbo.sp_hexadecimal') IS NOT NULL
       DROP PROCEDURE dbo.sp_hexadecimal
   GO
   CREATE PROCEDURE dbo.sp_hexadecimal
       @binvalue [varbinary](256)
       ,@hexvalue [nvarchar] (514) OUTPUT
   AS
   BEGIN
       DECLARE @i [smallint]
       DECLARE @length [smallint]
       DECLARE @hexstring [nchar](16)
       SELECT @hexvalue = N'0x'
       SELECT @i = 1
       SELECT @length = DATALENGTH(@binvalue)
       SELECT @hexstring = N'0123456789ABCDEF'
       WHILE (@i < =  @length)
       BEGIN
           DECLARE @tempint   [smallint]
           DECLARE @firstint  [smallint]
           DECLARE @secondint [smallint]
           SELECT @tempint = CONVERT([smallint], SUBSTRING(@binvalue, @i, 1))
           SELECT @firstint = FLOOR(@tempint / 16)
           SELECT @secondint = @tempint - (@firstint * 16)
           SELECT @hexvalue = @hexvalue
               + SUBSTRING(@hexstring, @firstint  + 1, 1)
               + SUBSTRING(@hexstring, @secondint + 1, 1)
           SELECT @i = @i + 1
       END
   END
   GO
   IF OBJECT_ID('dbo.sp_help_revlogin') IS NOT NULL
       DROP PROCEDURE dbo.sp_help_revlogin
   GO
   CREATE PROCEDURE dbo.sp_help_revlogin
       @login_name [sysname] = NULL
   AS
   BEGIN
       DECLARE @name                  [sysname]
       DECLARE @type                  [nvarchar](1)
       DECLARE @hasaccess             [int]
       DECLARE @denylogin             [int]
       DECLARE @is_disabled           [int]
       DECLARE @PWD_varbinary         [varbinary](256)
       DECLARE @PWD_string            [nvarchar](514)
       DECLARE @SID_varbinary         [varbinary](85)
       DECLARE @SID_string            [nvarchar](514)
       DECLARE @tmpstr                [nvarchar](4000)
       DECLARE @is_policy_checked     [nvarchar](3)
       DECLARE @is_expiration_checked [nvarchar](3)
       DECLARE @Prefix                [nvarchar](4000)
       DECLARE @defaultdb             [sysname]
       DECLARE @defaultlanguage       [sysname]
       DECLARE @tmpstrRole            [nvarchar](4000)
       IF @login_name IS NULL
       BEGIN
           DECLARE login_curs CURSOR
           FOR
           SELECT p.[sid],p.[name],p.[type],p.is_disabled,p.default_database_name,l.hasaccess,l.denylogin,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
           FROM sys.server_principals p
           LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
           WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
               AND p.[name] <> 'sa'
               AND p.[name] not like '##%'
           ORDER BY p.[name]
       END
       ELSE
           DECLARE login_curs CURSOR
           FOR
           SELECT p.[sid],p.[name],p.[type],p.is_disabled,p.default_database_name,l.hasaccess,l.denylogin,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
           FROM sys.server_principals p
           LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
           WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
               AND p.[name] <> 'sa'
               AND p.[name] NOT LIKE '##%'
               AND p.[name] = @login_name
           ORDER BY p.[name]
       OPEN login_curs
       FETCH NEXT FROM login_curs INTO @SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denylogin,@defaultlanguage
       IF (@@fetch_status = - 1)
       BEGIN
           PRINT '/* No login(s) found for ' + QUOTENAME(@login_name) + N'. */'
           CLOSE login_curs
           DEALLOCATE login_curs
           RETURN - 1
       END
       SET @tmpstr = N'/* sp_help_revlogin script
   ** Generated ' + CONVERT([nvarchar], GETDATE()) + N' on ' + @@SERVERNAME + N'
   */'
       PRINT @tmpstr
       WHILE (@@fetch_status <> - 1)
       BEGIN
           IF (@@fetch_status <> - 2)
           BEGIN
               PRINT ''
               SET @tmpstr = N'/* Login ' + QUOTENAME(@name) + N' */'
               PRINT @tmpstr
               SET @tmpstr = N'IF NOT EXISTS (
       SELECT 1
       FROM sys.server_principals
       WHERE [name] = N''' + @name + N'''
       )
   BEGIN'
               PRINT @tmpstr
               IF @type IN ('G','U') -- NT-authenticated Group/User
               BEGIN -- NT authenticated account/group 
                   SET @tmpstr = N'    CREATE LOGIN ' + QUOTENAME(@name) + N'
       FROM WINDOWS
       WITH DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
           ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
               END
               ELSE
               BEGIN -- SQL Server authentication
                   -- obtain password and sid
                   SET @PWD_varbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS [varbinary](256))
                   EXEC dbo.sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                   EXEC dbo.sp_hexadecimal @SID_varbinary, @SID_string OUT
                   -- obtain password policy state
                   SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
                   FROM sys.sql_logins
                   WHERE [name] = @name
   
                   SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
                   FROM sys.sql_logins
                   WHERE [name] = @name
   
                   SET @tmpstr = NCHAR(9) + N'CREATE LOGIN ' + QUOTENAME(@name) + N'
       WITH PASSWORD = ' + @PWD_string + N' HASHED
           ,SID = ' + @SID_string + N'
           ,DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
           ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
   
                   IF @is_policy_checked IS NOT NULL
                   BEGIN
                       SET @tmpstr = @tmpstr + N'
           ,CHECK_POLICY = ' + @is_policy_checked
                   END
   
                   IF @is_expiration_checked IS NOT NULL
                   BEGIN
                       SET @tmpstr = @tmpstr + N'
           ,CHECK_EXPIRATION = ' + @is_expiration_checked
                   END
               END
               IF (@denylogin = 1)
               BEGIN -- login is denied access
                   SET @tmpstr = @tmpstr
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'DENY CONNECT SQL TO ' + QUOTENAME(@name)
               END
               ELSE IF (@hasaccess = 0)
               BEGIN -- login exists but does not have access
                   SET @tmpstr = @tmpstr
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'REVOKE CONNECT SQL TO ' + QUOTENAME(@name)
               END
               IF (@is_disabled = 1)
               BEGIN -- login is disabled
                   SET @tmpstr = @tmpstr
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE'
               END
               SET @Prefix =
                   NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                   + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'EXEC [master].dbo.sp_addsrvrolemember @loginame = N'''
               SET @tmpstrRole = N''
               SELECT @tmpstrRole = @tmpstrRole
                   + CASE WHEN sysadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''sysadmin''' ELSE '' END
                   + CASE WHEN securityadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''securityadmin''' ELSE '' END
                   + CASE WHEN serveradmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''serveradmin''' ELSE '' END
                   + CASE WHEN setupadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''setupadmin''' ELSE '' END
                   + CASE WHEN processadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''processadmin''' ELSE '' END
                   + CASE WHEN diskadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''diskadmin''' ELSE '' END
                   + CASE WHEN dbcreator = 1 THEN @Prefix + LoginName + N''', @rolename = N''dbcreator''' ELSE '' END
                   + CASE WHEN bulkadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''bulkadmin''' ELSE '' END
               FROM (
                   SELECT
                       SUSER_SNAME([sid])AS LoginName
                       ,sysadmin
                       ,securityadmin
                       ,serveradmin
                       ,setupadmin
                       ,processadmin
                       ,diskadmin
                       ,dbcreator
                       ,bulkadmin
                   FROM sys.syslogins
                   WHERE (    sysadmin <> 0
                           OR securityadmin <> 0
                           OR serveradmin <> 0
                           OR setupadmin <> 0
                           OR processadmin <> 0
                           OR diskadmin <> 0
                           OR dbcreator <> 0
                           OR bulkadmin <> 0
                           )
                       AND [name] = @name
                   ) L
               IF @tmpstr <> '' PRINT @tmpstr
               IF @tmpstrRole <> '' PRINT @tmpstrRole
               PRINT 'END'
           END
           FETCH NEXT FROM login_curs INTO @SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denylogin,@defaultlanguage
       END
       CLOSE login_curs
       DEALLOCATE login_curs
       RETURN 0
   END
   

   备注

   此脚本会在“master”数据库中创建两个存储过程。 过程命名 为sp_hexadecimal 和 sp_help_revlogin。

2. 在 SSMS 查询编辑器中，选择“ 结果到文本 ”选项。

3. 在同一查询窗口或新查询窗口中运行下列语句：

   EXEC sp_help_revlogin
   

4. 由sp_help_revlogin存储过程生成的输出脚本是登录脚本。 此登录脚本创建具有原始安全标识符 (SID) 和原始密码的登录名。

5. 在继续在目标服务器上执行步骤之前，在 传输 SQL Server 登录部分时 ，请查看并遵循“其他注意事项”部分中的信息。

6. 实施“传输 SQL Server 登录名时的其他注意事项”部分的任何适用步骤后，使用任意客户端工具（例如 SSMS）连接到目标服务器 B。

7. 运行作为服务器 A 输出 sp_helprevlogin 生成的脚本。

传输 SQL Server 登录名时的其他注意事项

在对服务器 B 的实例上运行输出脚本之前，请检查以下信息：

了解 SQL Server 登录传输中的密码哈希

可以通过以下方式对密码进行哈希处理：

• VERSION_SHA1：SQL Server 使用 SHA1 算法生成此哈希。 从 SQL Server 2000 到 SQL Server 2008 R2 的版本使用此哈希。
• VERSION_SHA2：SQL Server 使用 SHA2 512 算法生成此哈希。 SQL Server 2012 及更高版本中的版本使用此哈希。

输出脚本使用加密密码创建登录名。 HASHED CREATE LOGIN 语句中的参数会导致此行为。 该参数指示在 PASSWORD 参数之后输入的密码已被哈希处理。

在 SQL Server 登录传输期间处理域更改

如果源服务器和目标服务器位于不同的域中，请仔细查看输出脚本。 必须更改输出脚本，并将原始域名替换为语句中的 CREATE LOGIN 新域名。 在新域中授予访问权限的集成登录名与原始域中的登录名没有相同的 SID。 因此，用户因此会与这些登录凭据失去关联。 有关解决孤立用户的详细信息，请参阅 孤立用户（SQL Server）故障排除 和 ALTER USER。

如果服务器 A 和服务器 B 处于同一域中，则使用相同的 SID。 因此，用户不会孤立。

查看和选择 SQL Server 登录名所需的权限

默认情况下，只有 sysadmin 固定服务器角色的成员才能从SELECT视图中运行sys.server_principals语句。 除非 sysadmin 固定服务器角色的成员向其他用户授予必要的权限，否则这些用户无法创建或运行输出脚本。

默认数据库设置未编写脚本并传输

本文中的步骤不会传输特定登录名的默认数据库信息。 因为默认数据库可能并不总是存在于服务器 B 上，所以存在此限制。若要为登录定义默认数据库，请使用 ALTER LOGIN 语句，并传入登录名和默认数据库作为参数。

管理 SQL Server 登录传输中的排序顺序差异

源服务器和目标服务器可能有不同的排序顺序，或者它们可能使用相同的排序顺序。 下面说明了如何解决每种情况：

• 不区分大小写的服务器 A 和区分大小写的服务器 B：服务器 A 的排序顺序不区分大小写，服务器 B 的排序顺序区分大小写。 在此情况下，在将登录名和密码传输到服务器 B 上的实例之后，必须以全部大写字母的形式来键入密码。

• 区分大小写的服务器 A 和不区分大小写的服务器 B： 服务器 A 的排序顺序区分大小写，服务器 B 的排序顺序不区分大小写。 在这种情况下，除非满足以下条件之一，否则用户无法使用登录名和传输到服务器 B 上的实例的密码登录：

  • 原始密码不包含字母。
  • 原始密码中的所有字母都是大写字母。

• 在两个服务器上区分大小写或不区分大小写：服务器 A 和服务器 B 的排序顺序要么区分大小写，要么不区分大小写。 在这些情况下，用户不会遇到问题。

解决目标服务器上的现有 SQL Server 登录名冲突

该脚本检查目标服务器上是否存在登录名，并且仅当登录名不存在时才创建一个登录名。 但是，如果在服务器 B 上的实例上运行输出脚本时收到以下错误消息，则必须按照本部分中的步骤手动解决冲突。

消息 15025, 级别 16, 状态 1, 行 1
服务器主体“MyLogin”已存在。

同样，服务器 B 上实例中的登录名可能具有与输出脚本中的 SID 相同的 SID。 在此情况下，在对服务器 B 上的实例运行输出脚本时，会接收到下面的错误消息：

Msg 15433，级别 16，状态 1，第 1 行提供的参数 sid 正在使用中。

若要手动解决冲突，请执行以下步骤：

1. 仔细检查输出脚本。
2. 检查服务器 B 上实例中视图的内容 sys.server_principals 。
3. 根据需要解决这些错误消息相关问题。

从 SQL Server 2005 开始，登录名的 SID 管理数据库级访问。 有时，当映射到不同数据库中的用户时，登录名可能具有不同的 SID。 如果手动合并不同服务器中的数据库，则可能会出现此问题。 在这种情况下，登录名只能访问数据库主体的 SID 与视图中的 SID sys.server_principals 匹配的数据库。 若要解决此问题，请使用 DROP USER 语句手动删除具有不匹配 SID 的数据库用户。 然后，使用 CREATE USER 语句再次添加用户，并将其映射到正确的登录名（服务器主体）。

有关详细信息以及将服务器与数据库主体区分开来，请参阅 CREATE USER 和 CREATE LOGIN。

参考

• 孤立用户故障排除
• CREATE LOGIN （Transact-SQL）
• ALTER LOGIN （Transact-SQL）
• 在 SQL Server Management Studio 中编写对象脚本