SQLServer的Login迁移脚本

背景:公司的数据由SQLServer2008 R2升级至SQLServer2012，并配置了AlwaysOn，本脚本用于将主节点的Login迁移至辅助节点。

1.在主节点执行以下脚本创建存储过程：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135

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.在主节点执行存储过程，导出创建Login的SQL语句：

?

1	EXEC sp_help_revlogin

 3.将第2步生成的脚本进行删减后在辅助节点执行。

 4.在主节点执行以下脚本，导出数据库用户的角色身份、对象权限，并在辅助节点执行：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

--  更改【此用户拥有的架构】 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='MyPrincipalName' --AND A.name='MyobjectName'   --  程序集权限查询及授予 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'