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

项目
10/17/2023

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

原始产品版本：SQL Server
原始 KB 编号： 918992，246133

简介

本文介绍如何在 Microsoft 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：在服务器 B) (目标SQL Server计算机上重置密码。

要解决此问题，请在 SQL Server 计算机中重置密码，然后为登录名编写脚本。

注意

重置密码时，将使用密码哈希算法。

方法 2：使用源服务器 A) 上生成的脚本将登录名和密码 ( (服务器 B) 传输到目标服务器。

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

  USE [master]
  GO
  IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
  GO
  CREATE PROCEDURE [dbo].[sp_hexadecimal]
  (
      @binvalue varbinary(256),
      @hexvalue varchar (514) OUTPUT
  )
  AS
  BEGIN
      DECLARE @charvalue varchar (514)
      DECLARE @i int
      DECLARE @length int
      DECLARE @hexstring char(16)
      SELECT @charvalue = '0x'
      SELECT @i = 1
      SELECT @length = DATALENGTH (@binvalue)
      SELECT @hexstring = '0123456789ABCDEF'

      WHILE (@i <= @length)
      BEGIN
            DECLARE @tempint int
            DECLARE @firstint int
            DECLARE @secondint int

            SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
            SELECT @firstint = FLOOR(@tempint/16)
            SELECT @secondint = @tempint - (@firstint*16)
            SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1)

            SELECT @i = @i + 1
      END 
      SELECT @hexvalue = @charvalue
  END
  go
  IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
  GO
  CREATE PROCEDURE [dbo].[sp_help_revlogin]   
  (
      @login_name sysname = NULL 
  )
  AS
  BEGIN
      DECLARE @name                     SYSNAME
      DECLARE @type                     VARCHAR (1)
      DECLARE @hasaccess                INT
      DECLARE @denylogin                INT
      DECLARE @is_disabled              INT
      DECLARE @PWD_varbinary            VARBINARY (256)
      DECLARE @PWD_string               VARCHAR (514)
      DECLARE @SID_varbinary            VARBINARY (85)
      DECLARE @SID_string               VARCHAR (514)
      DECLARE @tmpstr                   VARCHAR (1024)
      DECLARE @is_policy_checked        VARCHAR (3)
      DECLARE @is_expiration_checked    VARCHAR (3)
      Declare @Prefix                   VARCHAR(255)
      DECLARE @defaultdb                SYSNAME
      DECLARE @defaultlanguage          SYSNAME     
      DECLARE @tmpstrRole               VARCHAR (1024)

  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, p.default_language_name  
          FROM  sys.server_principals p 
          LEFT JOIN sys.syslogins     l ON ( l.name = p.name ) 
          WHERE p.type IN ( 'S', 'G', 'U' ) 
          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, p.default_language_name  
              FROM  sys.server_principals p 
              LEFT JOIN sys.syslogins        l ON ( l.name = p.name ) 
              WHERE p.type IN ( 'S', 'G', 'U' ) 
                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.'
                CLOSE login_curs
                DEALLOCATE login_curs
                RETURN -1
          END

          SET @tmpstr = '/* sp_help_revlogin script '
          PRINT @tmpstr

          SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

          PRINT @tmpstr
          PRINT ''

          WHILE (@@fetch_status <> -1)
          BEGIN
            IF (@@fetch_status <> -2)
            BEGIN
                  PRINT ''

                  SET @tmpstr = '-- Login: ' + @name

                  PRINT @tmpstr

                  SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''')
                  BEGIN'
                  Print @tmpstr 

                  IF (@type IN ( 'G', 'U'))
                  BEGIN -- NT authenticated account/group 
                    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'
                  END
                  ELSE 
                  BEGIN -- SQL Server authentication
                          -- obtain password and sid
                          SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

                          EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                          EXEC 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 = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' 
                                          + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'

                          IF ( @is_policy_checked IS NOT NULL )
                          BEGIN
                            SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
                          END

                          IF ( @is_expiration_checked IS NOT NULL )
                          BEGIN
                            SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
                          END
          END

          IF (@denylogin = 1)
          BEGIN -- login is denied access
              SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
          END
          ELSE IF (@hasaccess = 0)
          BEGIN -- login exists but does not have access
              SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
          END
          IF (@is_disabled = 1)
          BEGIN -- login is disabled
              SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
          END 

          SET @Prefix = '
          EXEC master.dbo.sp_addsrvrolemember @loginame='''

          SET @tmpstrRole=''

          SELECT @tmpstrRole = @tmpstrRole
              + CASE WHEN sysadmin        = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin'''        ELSE '' END
              + CASE WHEN securityadmin   = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin'''   ELSE '' END
              + CASE WHEN serveradmin     = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin'''     ELSE '' END
              + CASE WHEN setupadmin      = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin'''      ELSE '' END
              + CASE WHEN processadmin    = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin'''    ELSE '' END
              + CASE WHEN diskadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin'''       ELSE '' END
              + CASE WHEN dbcreator       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator'''       ELSE '' END
              + CASE WHEN bulkadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin'''       ELSE '' END
            FROM (
                      SELECT CONVERT(VARCHAR(100),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 

              PRINT @tmpstr
              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。

在 SSMS 查询编辑器中，选择“结果转文本”选项。
在同一查询窗口或新查询窗口中运行下列语句：
```
EXEC sp_help_revlogin
```
由sp_help_revlogin存储过程生成的输出脚本是登录脚本。此登录脚本创建具有原始安全标识符 (SID) 和原始密码的登录名。

重要

在继续在目标服务器上实施步骤之前，请查看以下 “备注 ”部分中的信息。

目标服务器（服务器 B）上的步骤：

使用 SSMS) 等任何客户端工具 (连接到服务器 B，然后运行步骤 4 中生成的脚本， (从服务器 A 输出 sp_helprevlogin) 。

备注

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

可以通过下列方式对密码进行哈希计算：
- VERSION_SHA1：此哈希使用 SHA1 算法生成，并且在 SQL Server 2000 到 SQL Server 2008 R2 中使用。
- VERSION_SHA2：此哈希使用 SHA2 512 算法生成，并且在 SQL Server 2012 和更高版本中使用。
仔细检查输出脚本。如果服务器 A 和服务器 B 位于不同的域中，则必须更改输出脚本。然后，必须使用语句中的 CREATE LOGIN 新域名替换原始域名。在新域中被授予访问权限的集成登录名与原始域中的登录名的 SID 不同。因此，用户会从这些登录名中孤立出来。有关如何解决这些孤立用户的详细信息，请参阅排查孤立用户 (SQL Server) 和 ALTER USER。
如果服务器 A 和服务器 B 处于同一域中，则使用相同的 SID。因此，用户不可能是孤立的。
在输出脚本中，通过使用加密密码来创建登录名。这是因为 CREATE LOGIN 语句中的 HASHED 参数。此参数指定在 PASSWORD 参数后输入的密码已经过哈希处理。
默认情况下，只有“sysadmin”固定服务器角色的成员可以从 sys.server_principals 视图运行 SELECT 语句。除非 sysadmin 固定服务器角色的成员向用户授予必要的权限，否则用户无法创建或运行输出脚本。
本文中的步骤不会传输特定登录名的默认数据库信息。这是因为服务器 B 上可能并不总是存在默认数据库。若要定义登录名的默认数据库，请使用 ALTER LOGIN 语句，方法是将登录名和默认数据库作为参数传入。
对源服务器和目标服务器进行排序：
- 不区分大小写的服务器 A 和区分大小写的服务器 B：服务器 A 的排序顺序可能不区分大小写，服务器 B 的排序顺序可能区分大小写。在此情况下，在将登录名和密码传输到服务器 B 上的实例之后，必须以全部大写字母的形式来键入密码。
- 区分大小写的服务器 A 和不区分大小写的服务器 B： 服务器 A 的排序顺序可能区分大小写，服务器 B 的排序顺序可能不区分大小写。在这种情况下，除非满足以下条件之一，否则用户无法使用您传输到服务器 B 上的实例的登录名和密码登录：
  - 原始密码不包含字母。
  - 原始密码中的所有字母都是大写字母。
- 在两个服务器上区分大小写或区分大小写：服务器 A 和服务器 B 的排序顺序可能区分大小写，或者服务器 A 和服务器 B 的排序顺序可能不区分大小写。在这些情况下，用户不会遇到问题。
服务器 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。在此情况下，该登录名只能访问具有与 sys.server_principals 视图中的 SID 匹配的 SID 的数据库。如果两个数据库来自不同服务器组合，则可能会出现此问题。若要解决此问题，可使用 DROP USERT 语句，从具有不匹配的 SID 的数据库中手动删除相应的登录名。然后，通过使用 CREATE USER 语句再次添加该登录。

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

简介

更多信息

目标服务器（服务器 B）上的步骤：

备注

参考

反馈

反馈

其他资源