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

本文介绍如何在 Windows 上运行的 SQL Server 的不同实例之间传输登录名和密码。

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

简介

本文介绍如何在 Microsoft SQL Server 的不同实例之间传输登录名和密码。 这些实例可能位于同一服务器或不同的服务器上，其版本可能有所不同。

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

在本文中，服务器 A 和服务器 B 是服务器。

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

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

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

请记住，错误 18456 由于许多其他原因而发生。 有关这些原因及其解决方法的详细信息，请参阅 MSSQLSERVER_18456。

传输登录名的步骤

要传输登录名，请根据具体情况采用下列方法之一。

方法 1：通过源服务器上的 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 上的密码并重新启用这些登录名。

方法 2：使用源服务器上生成的脚本（服务器 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. 在继续执行目标服务器上的步骤之前，请查看并遵循“ 备注 ”部分中的信息。

6. 从“备注”部分实现任何适用的步骤后，使用任何客户端工具（如 SSMS）连接到目标服务器 B。

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

注解

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

密码哈希信息

• 可以通过下列方式对密码进行哈希计算：

  • VERSION_SHA1：此哈希使用 SHA1 算法生成，并且在 SQL Server 2000 到 SQL Server 2008 R2 中使用。
  • VERSION_SHA2：此哈希使用 SHA2 512 算法生成，并且在 SQL Server 2012 和更高版本中使用。

• 在输出脚本中，通过使用加密密码来创建登录名。 这是因为 HASHED 语句中的 CREATE LOGIN 参数。 此参数指定在参数已哈希处理后 PASSWORD 输入的密码。

如何处理域更改

源服务器和目标服务器是否位于不同的域中？ 仔细检查输出脚本。 如果服务器 A 和服务器 B 位于不同的域中，则必须更改输出脚本。 然后，必须使用语句中的 CREATE LOGIN 新域名替换原始域名。 在新域中授予访问权限的集成登录名与原始域中的登录名没有相同的 SID。 因此，用户会从这些登录名中孤立出来。 有关如何解决这些孤立用户的详细信息，请参阅 对孤立用户（SQL Server） 和 ALTER USER 进行故障排除。

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

查看和选择所有登录名的权限

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

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

本文中的步骤不会传输特定登录名的默认数据库信息。 这是因为默认数据库可能始终存在于服务器 B 上。若要为登录名定义默认数据库，请使用 ALTER LOGIN 该语句，方法是传入登录名和默认数据库作为参数。

如何处理源服务器和目标服务器之间的不同排序顺序

源服务器和目标服务器之间的排序顺序可能存在差异，或者它们可能相同。 下面介绍了如何解决每个方案：

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

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

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

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

如何处理目标服务器上已存在的登录名

该脚本旨在检查目标服务器上是否存在登录名，并且仅当登录名不存在时才创建登录名。 但是，如果在服务器 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 中编写对象脚本