（5.3.1）数据库迁移——数据库迁移解决孤立用户与权限问题

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

注意实例可能位于同一服务器上，也可能位于不同的服务器上，其版本可能不同。

有关如何在其他版本的SQL Server的实例之间传输登录名和密码的详细信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：

246133如何在运行旧版SQL Server的SQL Server实例之间传输登录名和密码

【1】迁移登录账户方法

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

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

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

出现此问题的原因是您没有将登录名和密码从服务器A上的SQL Server实例

传输到服务器B上的SQL Server实例。要传输登录名，请根据您的具体情况使用下列方法之一。

方法1：重置目标SQL Server计算机上的密码（服务器B）

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

注意重置密码时使用密码哈希算法。

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

【2】使用SP获取登录脚本的T-SQL

要创建具有空密码的登录脚本，请按照下列步骤操作：

在服务器A上，启动SQL Server Management Studio，然后连接到从中移动数据库的SQL Server实例。

打开一个新的查询编辑器窗口，然后运行以下脚本。登录名

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
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
GO
 
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
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 @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin 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'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin 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
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
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
    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 + ']'

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

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

exec sp_help_revlogin

权限

--查看登录名服务器角色
SELECT sp.name AS [login_name]  
,CASE WHEN sp.[type]='S' THEN 'SQL 登录名'    
    WHEN sp.[type]='U' THEN 'Windows 登录名'    
    WHEN sp.[type]='G' THEN 'Windows 组'    
    WHEN sp.[type]='R' THEN '服务器角色'    
    WHEN sp.[type]='C' THEN '映射到证书的登录名'    
    WHEN sp.[type]='K' THEN '映射到非对称密钥的登录名'    
END AS [principal_type]  
,sp.is_disabled  
,ISNULL(sp.default_database_name,'') as [default_database_name]  
,ISNULL(rsp.name,'') AS [server_role]    
,STUFF((SELECT ','+permission_name FROM sys.server_permissions spp where sp.principal_id=spp.grantee_principal_id for xml path('')),1,1,'') as [permissions]  
FROM sys.server_principals sp     
LEFT JOIN sys.server_role_members srm ON sp.principal_id=srm.member_principal_id     
LEFT JOIN sys.server_principals rsp ON srm.role_principal_id=rsp.principal_id   
where rsp.name  is not null
ORDER BY [principal_type],sp.principal_id 
--授权服务器角色
select N'EXEC sp_addsrvrolemember  N''' +sp.name+ ''' ,N''' + rsp.name+''' '
FROM sys.server_principals sp     
LEFT JOIN sys.server_role_members srm ON sp.principal_id=srm.member_principal_id     
LEFT JOIN sys.server_principals rsp ON srm.role_principal_id=rsp.principal_id   
where rsp.name  is not null

--  将登录名添加为某个服务器级角色的成员  
EXEC sp_addsrvrolemember @loginame= 'kk' ,@rolename = 'sysadmin' 
master库中的用户名及权限可以用如下脚本进行迁移。
--  授予【数据库角色成员身份】权限  
SELECT 'exec sp_addrolemember N'''+g.name+''', N'''+u.name+''''  
FROM sys.database_principals u  
inner join sys.database_role_members m on u.principal_id = m.member_principal_id  
inner join sys.database_principals g on g.principal_id = m.role_principal_id  
ORDER BY g.name,u.name  
--  授予【安全对象】权限  
SELECT N'grant '+B.permission_name  collate chinese_prc_ci_ai_ws+N' on ['+A.name+N'] to ['+C.name+N']'  
FROM sys.sysobjects A(NOLOCK)   
INNER JOIN sys.database_permissions B(NOLOCK) ON A.id=B.major_id   
INNER JOIN sys.database_principals C(NOLOCK) ON B.grantee_principal_id=C.principal_id  
--WHERE C.name='kk' --A.name='objectName'

注意此脚本在master 数据库中创建两个存储过程。这些过程名为 sp_hexadecimal和sp_help_revlogin。

在相同或新的查询窗口中运行以下语句：
```
EXEC sp_help_revlogin
```
sp_help_revlogin存储过程生成的输出脚本是登录脚本。此登录脚本创建具有原始安全标识符（SID）和原始密码的登录名。

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

在服务器B上，启动SQL Server Management Studio，然后连接到移动数据库的SQL Server实例。

重要信息在转到第2步之前，请查看下面“备注”部分中的信息。
打开一个新的查询编辑器窗口，然后运行在前一过程的步骤2中生成的输出脚本。

备注

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

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

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

仔细查看输出脚本。如果服务器A和服务器B位于不同的域中，则必须更改输出脚本。然后，您必须使用CREATE LOGIN语句中的新域名替换原始域名。在新域中授予访问权限的集成登录名与原始域中的登录名不同。因此，用户从这些登录中孤立。有关如何解决这些孤立用户的更多信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：

240872如何在运行SQL Server的服务器之间移动数据库时解决权限问题

如果服务器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正在使用中。

因此，您必须执行以下操作：
1. 仔细查看输出脚本。
2. 检查服务器B上实例中sys.server_principals视图的内容。
3. 适当地解决这些错误消息。
  
  在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之前的密码登录时，密码哈希会自动更新。

【3】使用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%'

参考

有关如何解决孤立用户问题的详细信息，请转到孤立用户 Microsoft Developer Network（MSDN）网站疑难解答。

有关CREATE LOGIN语句的详细信息，请转到CREATE LOGIN（Transact-SQL） MSDN网站。

有关ALTER LOGIN语句的详细信息，请转到ALTER LOGIN（Transact-SQL） MSDN网站。

转自：https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

参考：KK博客：https://blog.csdn.net/kk185800961/article/details/43309883