# SQL 2008 完整迁移账户教程

By [eastdao](https://paragraph.com/@eastdao) · 2022-05-08

---

参考教程

[SQL Server 中登录账号与数据库用户迁移\_KK 笔记：专注数据【合作请私信】-CSDN博客\_sqlserver数据库用户权限迁移](https://blog.csdn.net/kk185800961/article/details/43309883)

完整操作

一，还原数据库

二，创建登录脚本

1.创建存储过程，生成 “创建登录账户”脚本

    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
    

2。执行存储过程，生成登录名的创建脚本

    EXEC sp_help_revlogin
    

3.生成的脚本大致如下

    /* sp_help_revlogin script 
    ** Generated 07 23 2020  9:45AM on GF-EB-TEST02 */
     
     
    -- Login: ##MS_PolicyTsqlExecutionLogin##
    CREATE LOGIN [##MS_PolicyTsqlExecutionLogin##] WITH PASSWORD = 0x0100F2CC726312A906A422C6794097233F0B032499B8C04838CF HASHED, SID = 0x014EA8886B841C4CA1F7ED32489BBF62, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyTsqlExecutionLogin##] DISABLE
     
    -- Login: NT AUTHORITY\SYSTEM
    CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: NT SERVICE\MSSQLSERVER
    CREATE LOGIN [NT SERVICE\MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: ZT158\admin-zmian
    CREATE LOGIN [ZT158\admin-zmian] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: NT SERVICE\SQLSERVERAGENT
    CREATE LOGIN [NT SERVICE\SQLSERVERAGENT] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: ZT158\sch
    CREATE LOGIN [ZT158\sch] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: chint
    CREATE LOGIN [chint] WITH PASSWORD = 0x01002C01EE46AB233A8400EE1065A0F659B2B9294971A05B7BFE HASHED, SID = 0xFFDA030855CEEC4EA1BD3382C4119D98, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: chintmis
    CREATE LOGIN [chintmis] WITH PASSWORD = 0x0100E3F06E612341F47C17112A6E6E25337217F5E6AEBBE5B261 HASHED, SID = 0xA985380BF1845149B420A75BEF018DF3, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
     
    -- Login: GF-EB-TEST02\EB01
    CREATE LOGIN [GF-EB-TEST02\EB01] FROM WINDOWS WITH DEFAULT_DATABASE = [master]; DENY CONNECT SQL TO [GF-EB-TEST02\EB01]; ALTER LOGIN [GF-EB-TEST02\EB01] DISABLE
     
    -- Login: ZT158\lh
    CREATE LOGIN [ZT158\lh] FROM WINDOWS WITH DEFAULT_DATABASE = [master]; DENY CONNECT SQL TO [ZT158\lh]; ALTER LOGIN [ZT158\lh] DISABLE
     
    -- Login: ZT158\nbuadmin
    CREATE LOGIN [ZT158\nbuadmin] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: ZT158\securityservice
    CREATE LOGIN [ZT158\securityservice] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: LinkServer_User
    CREATE LOGIN [LinkServer_User] WITH PASSWORD = 0x0100E2B923627E06ED7A03C2DE7BF5FAF903E0E33E232FF02070 HASHED, SID = 0xE86B12217D0CDC4783959A6D3959F870, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; DENY CONNECT SQL TO [LinkServer_User]
     
    -- Login: ybuser
    CREATE LOGIN [ybuser] WITH PASSWORD = 0x0100F224AA4B231C2CBD1C68B74F5642AB2A8CE00FB3E8359D1E HASHED, SID = 0xCA794917876C374BB47898C2ED88D3E9, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; DENY CONNECT SQL TO [ybuser]; ALTER LOGIN [ybuser] DISABLE
     
    -- Login: ybuserNew
    CREATE LOGIN [ybuserNew] WITH PASSWORD = 0x01008C2A7D0412C236CA05B86B7D7DBB81AD90A7E1D8414F92EF HASHED, SID = 0x7B388D3AB8DD434CAEE1418113D2B8AE, DEFAULT_DATABASE = [ECDATA], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; DENY CONNECT SQL TO [ybuserNew]; ALTER LOGIN [ybuserNew] DISABLE
     
    -- Login: ZT158\zhangjf
    CREATE LOGIN [ZT158\zhangjf] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: ZT158\hugang
    CREATE LOGIN [ZT158\hugang] FROM WINDOWS WITH DEFAULT_DATABASE = [master]; DENY CONNECT SQL TO [ZT158\hugang]; ALTER LOGIN [ZT158\hugang] DISABLE
     
    -- Login: ZT158\psli
    CREATE LOGIN [ZT158\psli] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: sap_r3_p01
    CREATE LOGIN [sap_r3_p01] WITH PASSWORD = 0x01008248BCE6366CECF73E3C984B39468CE3D68D06DD9C8AE03A HASHED, SID = 0x243F78CEE1A9874080D28F9589B2182B, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: RemoteUser_yb
    CREATE LOGIN [RemoteUser_yb] WITH PASSWORD = 0x0100D46BFA7F125C2690CD7DF1E835D27045CFE167B44B0A73B6 HASHED, SID = 0x043F2B259C74274387219E618AF3C390, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: pdtwebservice
    CREATE LOGIN [pdtwebservice] WITH PASSWORD = 0x0100655B507DEEF01D85F7314BDCA665B83DBFD1CECAAFA3FD92 HASHED, SID = 0x8B21695DF6C29D498D552F5D512C6D7E, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ZT158\sqlmonitor
    CREATE LOGIN [ZT158\sqlmonitor] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: sqlmonitor
    CREATE LOGIN [sqlmonitor] WITH PASSWORD = 0x0100F9FA4ECC41FD1D70B709C39C36E30677418BEA7842304675 HASHED, SID = 0xBCC82396447286479CF6A5CE141AC1BB, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF; DENY CONNECT SQL TO [sqlmonitor]; ALTER LOGIN [sqlmonitor] DISABLE
     
    -- Login: chintbs
    CREATE LOGIN [chintbs] WITH PASSWORD = 0x010004F394FAFB7EEFA7FDA5490132D2C87D065766D470C17BFA HASHED, SID = 0x510E766939E55E44BBF37EE5C1BDCDFD, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: chintcs
    CREATE LOGIN [chintcs] WITH PASSWORD = 0x0100E9035607B8DB832F7297230AFDF02752706EBD30EEB807B0 HASHED, SID = 0xFCAC9CDFD37E3448B801E2F8AE9E839B, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ZT158\smsadmin
    CREATE LOGIN [ZT158\smsadmin] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: KeyUser
    CREATE LOGIN [KeyUser] WITH PASSWORD = 0x010045163BEE263726DC1562A5DFA1C6C6DD01E5D21DF1B10B37 HASHED, SID = 0xA8982D3298F1794BB3A2DBBA2E1E5295, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: N9User
    CREATE LOGIN [N9User] WITH PASSWORD = 0x0100BE17A4DBC88039BCFFFC37D481A9591F0E9AAED1CE38B568 HASHED, SID = 0x2CEFC0BA7886484C884FF599FCC234F1, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ZT158\admin-xy
    CREATE LOGIN [ZT158\admin-xy] FROM WINDOWS WITH DEFAULT_DATABASE = [master]; DENY CONNECT SQL TO [ZT158\admin-xy]; ALTER LOGIN [ZT158\admin-xy] DISABLE
     
    -- Login: CHINTCrm
    CREATE LOGIN [CHINTCrm] WITH PASSWORD = 0x01004A6B2997180DC5BD3A6E28F8A1BB9C09CCC981ED3DD02DB3 HASHED, SID = 0xE12942627EB89642A8E109A2B32F495F, DEFAULT_DATABASE = [ECDATA], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ZT158\zxjian
    CREATE LOGIN [ZT158\zxjian] FROM WINDOWS WITH DEFAULT_DATABASE = [master]; DENY CONNECT SQL TO [ZT158\zxjian]; ALTER LOGIN [ZT158\zxjian] DISABLE
     
    -- Login: devuser01
    CREATE LOGIN [devuser01] WITH PASSWORD = 0x010013208D68211E7EA4B5E2C977F2BAB194F137816A99156864 HASHED, SID = 0x53228662985D604685550E31E3C3E7FD, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ##MS_PolicyEventProcessingLogin##
    CREATE LOGIN [##MS_PolicyEventProcessingLogin##] WITH PASSWORD = 0x010005C136C8FE31D443D30A1EFA88EA051B354B846B4A67625E HASHED, SID = 0xEFDD479B99C2024F858386D0DD3385EB, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyEventProcessingLogin##] DISABLE
     
    -- Login: biinf
    CREATE LOGIN [biinf] WITH PASSWORD = 0x0100A46DDB318ECD57450FB0DFA56630C8D149CA5EC075E28477 HASHED, SID = 0x9453847EA9717B49B47FF12A24C6A815, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ybsales
    CREATE LOGIN [ybsales] WITH PASSWORD = 0x010061BE638484565E6E10D4884CE55D26ADA1369E7678BCBE0E HASHED, SID = 0xA9F76E8BD853E04C95483A0034E87785, DEFAULT_DATABASE = [ECDATA], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ybecpsales
    CREATE LOGIN [ybecpsales] WITH PASSWORD = 0x0100DE44251429612C4FA5A756909B75A09339989F2A7D81391F HASHED, SID = 0xB85950D5CB772445B043F83A3D5BEAB1, DEFAULT_DATABASE = [ECDATA], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: omsuser
    CREATE LOGIN [omsuser] WITH PASSWORD = 0x01007624B0721A646B0DC7CA4382285391E6E48B836DF459A6A4 HASHED, SID = 0x56675518F045EB4F9685ACEBA45DBA6D, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: fiuser
    CREATE LOGIN [fiuser] WITH PASSWORD = 0x010089E5F41239345B74A1FFAEA7608A006E69C5F135C4D3C2E8 HASHED, SID = 0xEB8768EA6E4CF04385D17AFFDF9B6FB9, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: cne
    CREATE LOGIN [cne] WITH PASSWORD = 0x01000E94CB8E27FA42C1C4866E36F1039ACEA529B0FBAA139365 HASHED, SID = 0xB26D73155BA46B4BA8C0AD66F17EDEE5, DEFAULT_DATABASE = [cne], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF; DENY CONNECT SQL TO [cne]; ALTER LOGIN [cne] DISABLE
     
    -- Login: CreditManageUser
    CREATE LOGIN [CreditManageUser] WITH PASSWORD = 0x01009E9D52A6D4A3FF699E2EEC6BD62D386035650821DB176D88 HASHED, SID = 0x48BD43F28A1F544292420CF8F9505DA0, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ZT158\admin-jinxj
    CREATE LOGIN [ZT158\admin-jinxj] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: ZT158\zhuangxf
    CREATE LOGIN [ZT158\zhuangxf] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: gysrm
    CREATE LOGIN [gysrm] WITH PASSWORD = 0x0100AC6C5ECB3872917B5BD2EC752A79B4F541F988E0A136B1F4 HASHED, SID = 0xBCA06C1E13A5334C9DE2E2EEE20E3A85, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: srmuser
    CREATE LOGIN [srmuser] WITH PASSWORD = 0x0100F90CAF803803DE11F363A6B5C3CA259C26D3DD2B5C1A3B5E HASHED, SID = 0xA22F09610EE74148A436C3A47978D88B, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ZT158\liuyang1
    CREATE LOGIN [ZT158\liuyang1] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: h3uer
    CREATE LOGIN [h3uer] WITH PASSWORD = 0x01004F58DE3AAB11A911E712AC5C69AEC440C664D3830D8A8A3A HASHED, SID = 0xE6EFCB02741F504DAC6384F20F81F105, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
    
    完成时间: 2020-07-23T09:45:17.3959852+08:00
    

这样迁移过去 只有登录权限

    --	更改【此用户拥有的架构】
    select s.name,p.name,'ALTER AUTHORIZATION ON SCHEMA::['+s.name+'] TO ['+p.name+']'
    from sys.schemas s inner join sys.database_principals p on s.principal_id=p.principal_id
    where s.name <> p.name
     
     
    --	授予【数据库角色成员身份】权限
    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'
    

默认是public的可以排除

授予增删改权限

    --	程序集权限查询及授予
    SELECT * FROM sys.types WHERE is_user_defined=1
    SELECT * FROM sys.table_types
     
    SELECT pms.state_desc,pms.permission_name,pms.class_desc,stt.name,tt.name,psp.name
    ,pms.state_desc+' '+pms.permission_name+' ON '+class_desc+'::['+stt.name+'].['+tt.name+'] TO ['+psp.name+']'
    collate Chinese_PRC_Stroke_CI_AS  
    FROM sys.table_types AS tt
    INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
    INNER JOIN sys.database_permissions AS pms ON pms.major_id=tt.user_type_id
    INNER JOIN sys.database_principals AS psp ON psp.principal_id = pms.grantee_principal_id
    WHERE pms.class=6 AND pms.minor_id=0 AND pms.state = 'G'
    

获取linkserver 密码

    https://blog.netspi.com/decrypting-mssql-database-link-server-passwords/
    

导入模块服务器管理器；添加-WindowsFeature PowerShell-ISE

调用函数

Set-ExecutionPolicy unrestricted -Scope 进程

获取 MSSQLLinkPasswords

解决孤立用户

参考教程

[SQL Server 中登录账号与数据库用户迁移\_KK 笔记：专注数据【合作请私信】-CSDN博客\_sqlserver数据库用户权限迁移](https://blog.csdn.net/kk185800961/article/details/43309883)

完整操作

一，还原数据库

二，创建登录脚本

1.创建存储过程，生成 “创建登录账户”脚本

    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
    

2。执行存储过程，生成登录名的创建脚本

    EXEC sp_help_revlogin
    

3.生成的脚本大致如下

    /* sp_help_revlogin script 
    ** Generated 07 23 2020  9:45AM on GF-EB-TEST02 */
     
     
    -- Login: ##MS_PolicyTsqlExecutionLogin##
    CREATE LOGIN [##MS_PolicyTsqlExecutionLogin##] WITH PASSWORD = 0x0100F2CC726312A906A422C6794097233F0B032499B8C04838CF HASHED, SID = 0x014EA8886B841C4CA1F7ED32489BBF62, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyTsqlExecutionLogin##] DISABLE
     
    -- Login: NT AUTHORITY\SYSTEM
    CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: NT SERVICE\MSSQLSERVER
    CREATE LOGIN [NT SERVICE\MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: ZT158\admin-zmian
    CREATE LOGIN [ZT158\admin-zmian] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: NT SERVICE\SQLSERVERAGENT
    CREATE LOGIN [NT SERVICE\SQLSERVERAGENT] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: ZT158\sch
    CREATE LOGIN [ZT158\sch] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: chint
    CREATE LOGIN [chint] WITH PASSWORD = 0x01002C01EE46AB233A8400EE1065A0F659B2B9294971A05B7BFE HASHED, SID = 0xFFDA030855CEEC4EA1BD3382C4119D98, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: chintmis
    CREATE LOGIN [chintmis] WITH PASSWORD = 0x0100E3F06E612341F47C17112A6E6E25337217F5E6AEBBE5B261 HASHED, SID = 0xA985380BF1845149B420A75BEF018DF3, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
     
    -- Login: GF-EB-TEST02\EB01
    CREATE LOGIN [GF-EB-TEST02\EB01] FROM WINDOWS WITH DEFAULT_DATABASE = [master]; DENY CONNECT SQL TO [GF-EB-TEST02\EB01]; ALTER LOGIN [GF-EB-TEST02\EB01] DISABLE
     
    -- Login: ZT158\lh
    CREATE LOGIN [ZT158\lh] FROM WINDOWS WITH DEFAULT_DATABASE = [master]; DENY CONNECT SQL TO [ZT158\lh]; ALTER LOGIN [ZT158\lh] DISABLE
     
    -- Login: ZT158\nbuadmin
    CREATE LOGIN [ZT158\nbuadmin] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: ZT158\securityservice
    CREATE LOGIN [ZT158\securityservice] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: LinkServer_User
    CREATE LOGIN [LinkServer_User] WITH PASSWORD = 0x0100E2B923627E06ED7A03C2DE7BF5FAF903E0E33E232FF02070 HASHED, SID = 0xE86B12217D0CDC4783959A6D3959F870, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; DENY CONNECT SQL TO [LinkServer_User]
     
    -- Login: ybuser
    CREATE LOGIN [ybuser] WITH PASSWORD = 0x0100F224AA4B231C2CBD1C68B74F5642AB2A8CE00FB3E8359D1E HASHED, SID = 0xCA794917876C374BB47898C2ED88D3E9, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; DENY CONNECT SQL TO [ybuser]; ALTER LOGIN [ybuser] DISABLE
     
    -- Login: ybuserNew
    CREATE LOGIN [ybuserNew] WITH PASSWORD = 0x01008C2A7D0412C236CA05B86B7D7DBB81AD90A7E1D8414F92EF HASHED, SID = 0x7B388D3AB8DD434CAEE1418113D2B8AE, DEFAULT_DATABASE = [ECDATA], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; DENY CONNECT SQL TO [ybuserNew]; ALTER LOGIN [ybuserNew] DISABLE
     
    -- Login: ZT158\zhangjf
    CREATE LOGIN [ZT158\zhangjf] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: ZT158\hugang
    CREATE LOGIN [ZT158\hugang] FROM WINDOWS WITH DEFAULT_DATABASE = [master]; DENY CONNECT SQL TO [ZT158\hugang]; ALTER LOGIN [ZT158\hugang] DISABLE
     
    -- Login: ZT158\psli
    CREATE LOGIN [ZT158\psli] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: sap_r3_p01
    CREATE LOGIN [sap_r3_p01] WITH PASSWORD = 0x01008248BCE6366CECF73E3C984B39468CE3D68D06DD9C8AE03A HASHED, SID = 0x243F78CEE1A9874080D28F9589B2182B, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: RemoteUser_yb
    CREATE LOGIN [RemoteUser_yb] WITH PASSWORD = 0x0100D46BFA7F125C2690CD7DF1E835D27045CFE167B44B0A73B6 HASHED, SID = 0x043F2B259C74274387219E618AF3C390, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: pdtwebservice
    CREATE LOGIN [pdtwebservice] WITH PASSWORD = 0x0100655B507DEEF01D85F7314BDCA665B83DBFD1CECAAFA3FD92 HASHED, SID = 0x8B21695DF6C29D498D552F5D512C6D7E, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ZT158\sqlmonitor
    CREATE LOGIN [ZT158\sqlmonitor] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: sqlmonitor
    CREATE LOGIN [sqlmonitor] WITH PASSWORD = 0x0100F9FA4ECC41FD1D70B709C39C36E30677418BEA7842304675 HASHED, SID = 0xBCC82396447286479CF6A5CE141AC1BB, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF; DENY CONNECT SQL TO [sqlmonitor]; ALTER LOGIN [sqlmonitor] DISABLE
     
    -- Login: chintbs
    CREATE LOGIN [chintbs] WITH PASSWORD = 0x010004F394FAFB7EEFA7FDA5490132D2C87D065766D470C17BFA HASHED, SID = 0x510E766939E55E44BBF37EE5C1BDCDFD, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: chintcs
    CREATE LOGIN [chintcs] WITH PASSWORD = 0x0100E9035607B8DB832F7297230AFDF02752706EBD30EEB807B0 HASHED, SID = 0xFCAC9CDFD37E3448B801E2F8AE9E839B, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ZT158\smsadmin
    CREATE LOGIN [ZT158\smsadmin] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: KeyUser
    CREATE LOGIN [KeyUser] WITH PASSWORD = 0x010045163BEE263726DC1562A5DFA1C6C6DD01E5D21DF1B10B37 HASHED, SID = 0xA8982D3298F1794BB3A2DBBA2E1E5295, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: N9User
    CREATE LOGIN [N9User] WITH PASSWORD = 0x0100BE17A4DBC88039BCFFFC37D481A9591F0E9AAED1CE38B568 HASHED, SID = 0x2CEFC0BA7886484C884FF599FCC234F1, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ZT158\admin-xy
    CREATE LOGIN [ZT158\admin-xy] FROM WINDOWS WITH DEFAULT_DATABASE = [master]; DENY CONNECT SQL TO [ZT158\admin-xy]; ALTER LOGIN [ZT158\admin-xy] DISABLE
     
    -- Login: CHINTCrm
    CREATE LOGIN [CHINTCrm] WITH PASSWORD = 0x01004A6B2997180DC5BD3A6E28F8A1BB9C09CCC981ED3DD02DB3 HASHED, SID = 0xE12942627EB89642A8E109A2B32F495F, DEFAULT_DATABASE = [ECDATA], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ZT158\zxjian
    CREATE LOGIN [ZT158\zxjian] FROM WINDOWS WITH DEFAULT_DATABASE = [master]; DENY CONNECT SQL TO [ZT158\zxjian]; ALTER LOGIN [ZT158\zxjian] DISABLE
     
    -- Login: devuser01
    CREATE LOGIN [devuser01] WITH PASSWORD = 0x010013208D68211E7EA4B5E2C977F2BAB194F137816A99156864 HASHED, SID = 0x53228662985D604685550E31E3C3E7FD, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ##MS_PolicyEventProcessingLogin##
    CREATE LOGIN [##MS_PolicyEventProcessingLogin##] WITH PASSWORD = 0x010005C136C8FE31D443D30A1EFA88EA051B354B846B4A67625E HASHED, SID = 0xEFDD479B99C2024F858386D0DD3385EB, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyEventProcessingLogin##] DISABLE
     
    -- Login: biinf
    CREATE LOGIN [biinf] WITH PASSWORD = 0x0100A46DDB318ECD57450FB0DFA56630C8D149CA5EC075E28477 HASHED, SID = 0x9453847EA9717B49B47FF12A24C6A815, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ybsales
    CREATE LOGIN [ybsales] WITH PASSWORD = 0x010061BE638484565E6E10D4884CE55D26ADA1369E7678BCBE0E HASHED, SID = 0xA9F76E8BD853E04C95483A0034E87785, DEFAULT_DATABASE = [ECDATA], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ybecpsales
    CREATE LOGIN [ybecpsales] WITH PASSWORD = 0x0100DE44251429612C4FA5A756909B75A09339989F2A7D81391F HASHED, SID = 0xB85950D5CB772445B043F83A3D5BEAB1, DEFAULT_DATABASE = [ECDATA], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: omsuser
    CREATE LOGIN [omsuser] WITH PASSWORD = 0x01007624B0721A646B0DC7CA4382285391E6E48B836DF459A6A4 HASHED, SID = 0x56675518F045EB4F9685ACEBA45DBA6D, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: fiuser
    CREATE LOGIN [fiuser] WITH PASSWORD = 0x010089E5F41239345B74A1FFAEA7608A006E69C5F135C4D3C2E8 HASHED, SID = 0xEB8768EA6E4CF04385D17AFFDF9B6FB9, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: cne
    CREATE LOGIN [cne] WITH PASSWORD = 0x01000E94CB8E27FA42C1C4866E36F1039ACEA529B0FBAA139365 HASHED, SID = 0xB26D73155BA46B4BA8C0AD66F17EDEE5, DEFAULT_DATABASE = [cne], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF; DENY CONNECT SQL TO [cne]; ALTER LOGIN [cne] DISABLE
     
    -- Login: CreditManageUser
    CREATE LOGIN [CreditManageUser] WITH PASSWORD = 0x01009E9D52A6D4A3FF699E2EEC6BD62D386035650821DB176D88 HASHED, SID = 0x48BD43F28A1F544292420CF8F9505DA0, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ZT158\admin-jinxj
    CREATE LOGIN [ZT158\admin-jinxj] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: ZT158\zhuangxf
    CREATE LOGIN [ZT158\zhuangxf] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: gysrm
    CREATE LOGIN [gysrm] WITH PASSWORD = 0x0100AC6C5ECB3872917B5BD2EC752A79B4F541F988E0A136B1F4 HASHED, SID = 0xBCA06C1E13A5334C9DE2E2EEE20E3A85, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: srmuser
    CREATE LOGIN [srmuser] WITH PASSWORD = 0x0100F90CAF803803DE11F363A6B5C3CA259C26D3DD2B5C1A3B5E HASHED, SID = 0xA22F09610EE74148A436C3A47978D88B, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
     
    -- Login: ZT158\liuyang1
    CREATE LOGIN [ZT158\liuyang1] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
     
    -- Login: h3uer
    CREATE LOGIN [h3uer] WITH PASSWORD = 0x01004F58DE3AAB11A911E712AC5C69AEC440C664D3830D8A8A3A HASHED, SID = 0xE6EFCB02741F504DAC6384F20F81F105, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
    
    完成时间: 2020-07-23T09:45:17.3959852+08:00
    

这样迁移过去 只有登录权限

    --	更改【此用户拥有的架构】
    select s.name,p.name,'ALTER AUTHORIZATION ON SCHEMA::['+s.name+'] TO ['+p.name+']'
    from sys.schemas s inner join sys.database_principals p on s.principal_id=p.principal_id
    where s.name <> p.name
     
     
    --	授予【数据库角色成员身份】权限
    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'
    

默认是public的可以排除

授予增删改权限

    --	程序集权限查询及授予
    SELECT * FROM sys.types WHERE is_user_defined=1
    SELECT * FROM sys.table_types
     
    SELECT pms.state_desc,pms.permission_name,pms.class_desc,stt.name,tt.name,psp.name
    ,pms.state_desc+' '+pms.permission_name+' ON '+class_desc+'::['+stt.name+'].['+tt.name+'] TO ['+psp.name+']'
    collate Chinese_PRC_Stroke_CI_AS  
    FROM sys.table_types AS tt
    INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
    INNER JOIN sys.database_permissions AS pms ON pms.major_id=tt.user_type_id
    INNER JOIN sys.database_principals AS psp ON psp.principal_id = pms.grantee_principal_id
    WHERE pms.class=6 AND pms.minor_id=0 AND pms.state = 'G'
    

获取linkserver 密码

    https://blog.netspi.com/decrypting-mssql-database-link-server-passwords/
    

Import-Module ServerManager; Add-WindowsFeature PowerShell-ISE

调用函数

Set-ExecutionPolicy unrestricted -Scope process

Get-MSSQLLinkPasswords

解决孤立用户

---

*Originally published on [eastdao](https://paragraph.com/@eastdao/sql-2008)*