• (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

    要创建具有空密码的登录脚本,请按照下列步骤操作:
    1. 在服务器A上,启动SQL Server Management Studio,然后连接到从中移动数据库的SQL Server实例。
    2. 打开一个新的查询编辑器窗口,然后运行以下脚本。登录名
      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_hexadecimalsp_help_revlogin
    3. 在相同或新的查询窗口中运行以下语句: 
      EXEC sp_help_revlogin
      sp_help_revlogin存储过程生成的输出脚本是登录脚本。此登录脚本创建具有原始安全标识符(SID)和原始密码的登录名。

     目标服务器上的步骤(服务器B):

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

      重要信息在转到第2步之前,请查看下面“备注”部分中的信息。
    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网站。
     
  • 相关阅读:
    Java多线程之赛跑游戏(含生成exe文件)
    JavaSE之绘制菱形
    JavaSE项目之员工收录系统
    深度解析continue,break和return
    如何查看yum安装路径
    转载 linux umount 时出现device is busy 的处理方法--fuser
    linux安装扩展总结
    linux 编译安装amqp
    vmware 实现linux目录映射window本地目录
    yaf学习之——生成yaf示例框架
  • 原文地址:https://www.cnblogs.com/gered/p/11511365.html
Copyright © 2020-2023  润新知