SQL Server数据库迁移-不同实例之间传输登录名和密码

SQL数据库运维 2022-11-01

1556

点击蓝色字关注“SQL数据库运维”，回复“SQL”获取2TB学习资源！

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

注意：这些实例可能位于同一服务器上，也可能位于不同的服务器上，并且它们的版本可能有所不同。

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

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

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

出现此问题的原因是，你未将登录名和密码从服务器 A 上的 SQL Server 实例传输到服务器 B 上的 SQL Server 实例。

注意

出现 18456 错误消息也可能是由于其他原因。

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

方法 1：重置目标 SQL Server 计算机（服务器 B）上的密码
要解决此问题，请在 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'
          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 + ']'
                  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”。

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

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

EXEC sp_help_revlogin

4.由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语句中的新域名替换原始域名。在新域中授予访问权限的集成登录名与原始域中的登录名不同。因此，用户会从这些登录名中孤立出来。有关如何解决这些孤立用户的更多信息，请复制以下提供的官网链接，以查看Microsoft知识库中相应的文章：当在运行 SQL Server 的服务器之间移动数据库时，如何解决权限问题；https://learn.microsoft.com/zh-CN/sql/t-sql/statements/alter-user-transact-sql?view=sql-server-ver15

如果服务器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上的实例上运行输出脚本时，您会收到以下错误消息：
消息15433，级别16，状态1，行1
提供的参数sid正在使用中。
因此，您必须执行以下操作：

仔细查看输出脚本。
检查服务器B上实例中sys.server_principals视图的内容。
适当地解决这些错误消息。

在SQL Server 2005中，登录的SID用于实现数据库级访问。登录可以在服务器上的不同数据库中具有不同的SID。在这种情况下，登录只能访问具有与sys.server_principals视图中的SID匹配的SID的数据库。如果两个数据库从不同的服务器组合，则可能会出现此问题。若要解决此问题，请使用DROP USER语句从具有SID不匹配的数据库中手动删除登录。然后，使用CREATE USER语句再次添加登录。

如果您尝试使用脚本化的SQL Server 2000之前的登录名创建新的SQL Server 2012登录，则会收到以下错误消息：

消息15021，级别16，状态2，行1
参数PASSWORD的值无效。指定有效的参数值。

注意您在SQL Server 2012中收到此错误，因为为CREATE LOGIN和ALTER LOGIN语句提供了16字节的密码哈希。

若要在运行SQL Server 2012的服务器上解决此问题，请创建一个密码为空的登录名。为此，请运行以下脚本：

CREATE LOGIN [Test] WITH PASSWORD = '', SID = 0x90FD605DCEFAE14FAB4D5EB0BBA1AECC, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF

创建具有空密码的登录名后，用户可以在下次登录尝试时更改密码。

方法3：使用SQL Server 2000之前的密码登录

注意仅当您将SQL Server 2000迁移到更新的受支持版本的SQL Server时，此方法才适用。

在这种情况下，请求用户使用SQL Server 2000之前的登录名登录到运行SQL Server的服务器。

注意当用户使用SQL Server 2000之前的密码登录时，密码哈希会自动更新。

使用T-SQL获取登录用户的T-SQL（推荐）

SELECT  'CREATE LOGIN [' + p.name + '] '
       + CASE WHEN p.type IN ( 'U', 'G' ) THEN 'FROM windows '
              ELSE ''
         END + 'WITH ' + CASE WHEN p.type = 'S'
                              THEN 'password = '
                                   + master.sys.fn_varbintohexstr(l.password_hash)
                                   + ' hashed, ' + 'sid = '
                                   + master.sys.fn_varbintohexstr(l.sid)
                                   + ', check_expiration = '
                                   + CASE WHEN l.is_expiration_checked > 0
                                          THEN 'ON, '
                                          ELSE 'OFF, '
                                     END + 'check_policy = '
                                   + CASE WHEN l.is_policy_checked > 0
                                          THEN 'ON, '
                                          ELSE 'OFF, '
                                     END
                                   + CASE WHEN l.credential_id > 0
                                          THEN 'credential = ' + c.name
                                               + ', '
                                          ELSE ''
                                     END
                              ELSE ''
                         END + 'default_database = '
       + p.default_database_name
       + CASE WHEN LEN(p.default_language_name) > 0
              THEN ', default_language = ' + p.default_language_name
              ELSE ''
         END
FROM    sys.server_principals p
       LEFT JOIN sys.sql_logins l
       ON p.principal_id = l.principal_id
       LEFT JOIN sys.credentials c
       ON l.credential_id = c.credential_id
WHERE   p.type IN ( 'S', 'U', 'G' )
       --AND p.name NOT IN ( 'sa')
               AND p.name NOT LIKE '%##%'
               AND p.name NOT LIKE '%NT SERVICE%'
               AND p.name NOT LIKE '%NT AUTHORITY%'

结果如下：

文章参考资料：https://www.cnblogs.com/gered/p/11511365.html

Microsoft官网：https://learn.microsoft.com/zh-CN/troubleshoot/sql/security/transfer-logins-passwords-between-instances

点击关注“SQL数据库运维”，后台或浏览至公众号文章底部点击“发消息”回复关键字：进群，带你进入高手如云的技术交流群。后台回复关键字：SQL，获取学习资料。

动动小手点击加关注呦☟☟☟

大小写数据库服务器 sql 数据库脚本 select

文章转载自SQL数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL Server数据库迁移-不同实例之间传输登录名和密码

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

备注

使用T-SQL获取登录用户的T-SQL（推荐）

评论