# SQL 2008 完整迁移账户教程

**Published by:** [eastdao](https://paragraph.com/@eastdao/)
**Published on:** 2022-05-08
**URL:** https://paragraph.com/@eastdao/sql-2008

## Content

参考教程 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 解决孤立用户 参考教程 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/ Import-Module ServerManager; Add-WindowsFeature PowerShell-ISE 调用函数 Set-ExecutionPolicy unrestricted -Scope process Get-MSSQLLinkPasswords 解决孤立用户

## Publication Information

- [eastdao](https://paragraph.com/@eastdao/): Publication homepage
- [All Posts](https://paragraph.com/@eastdao/): More posts from this publication
- [RSS Feed](https://api.paragraph.com/blogs/rss/@eastdao): Subscribe to updates
- [Twitter](https://twitter.com/east_dao): Follow on Twitter