SQL 2008 完整迁移账户教程

参考教程

SQL Server 中登录账号与数据库用户迁移_KK 笔记：专注数据【合作请私信】-CSDN博客_sqlserver数据库用户权限迁移

完整操作

一，还原数据库

二，创建登录脚本

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

解决孤立用户