Login migration script for SQL Server

Background: The company's data was upgraded from SQLServer2008 R2 to SQLServer2012, and AlwaysOn was configured to migrate the primary node login to the secondary node.

1. Execute the following script on the master node to create the stored procedure:

Use Mastergoif object_id (' sp_hexadecimal ') are not NULL DROP PROCEDURE sp_hexadecimalgocreate PROCEDURE sp_hexadecimal @binvalue varbinary, @hexvalue varchar (514) outputasdeclare @charvalue varchar (514) DECLARE @i intdeclare @lengt H intDECLARE @hexstring char (+) Select @charvalue = ' 0x ' select @i = 1SELECT @length = datalength (@binvalue) Select @hexstr   ing = ' 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 = @t Empint-(@firstint *16) SELECT @charvalue = @charvalue + SUBSTRING (@hexstring, @firstint +1, 1) + SUBSTRING (@hexstri  Ng, @secondint +1, 1) SELECT @i = @i + 1ENDSELECT @hexvalue = @charvalueGO IF object_id (' sp_help_revlogin ') is not NULL DROP PROCEDURE sp_help_revlogingocreate PROCEDURE sp_help_revlogin @login_name sysname = NULL asdeclare @name sysnamedecl is @type varchar (1) DECLis @hasaccess intdeclare @denylogin intdeclare @is_disabled intdeclare @PWD_varbinary varbinary (DECLARE) @PWD_strin G varchar (514) DECLARE @SID_varbinary varbinary () DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024x768) DECLA RE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name is N ULL) 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 joins 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.D Efault_database_name, L.hasaccess, l.denylogin from Sys.server_principals P left joins sys.syslogins l on (l.name = P . Name) WHERE P.type in (' S ', ' G ', ' U ') and p.name = @login_nameOPEN Login_cursfetch NEXT from login_curs to @SID_varb Inary, @name, @type, @is_Disabled, @defaultdb, @hasaccess, @denyloginIF (@ @fetch_status =-1) BEGIN PRINT ' No login (s) found. '  CLOSE login_curs deallocate login_curs return-1endset @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstrSET @tmpstr = ' * * Generated ' + CONVERT (varchar, GETDATE ()) + ' on ' + @ @SERVERNAME + ' * * ' PRINT @tmpstrPRINT ' while @ @fetch_status <>-1) Begin IF (@ @fetch_status <>-2) BEGIN print ' SET @tmpstr = '--Login: ' + @name PRINT @tmps  TR 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 Varbinar  Y ()) 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 If 1 then ' on ' while 0 Then ' OFF ' ELSE NULL END from Sys.sql_ Logins WHERE name = @name SELECT @is_expiration_checked = case is_expiration_checked If 1 then ' on ' while 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_check Ed END IF (@is_expiration_checked is not NULL) BEGIN SET @tmpstr = @tmpstr + ', CHECK_EXPI ration = ' + @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 has a CcESS SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL to ' + QUOTENAME (@name) END IF (@is_disabled = 1) BEGIN – login is disabled SET @tmpst r = @tmpstr + '; ALTER LOGIN ' + QUOTENAME (@name) + ' DISABLE ' end PRINT @tmpstr end FETCH NEXT from login_curs into @SID_varbina Ry, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin endclose login_cursdeallocate Login_cursreturn 0GO

2. Execute the stored procedure on the master node and export the SQL statement that created login:

3. The script generated by step 2nd is truncated and executed at the secondary node.

4. Execute the following script on the master node to export the role identity, object permissions of the database user, and execute on the secondary node:

--Change "schema owned by this user" 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_idwhere s.name <> p.name--Grant database role membership Permissions select ' EXEC sp_addrolemember n ' ' +g.name+ ', n ' ' +u.name+ ' ' from sys.database_principals Uinner join Sys.database _role_members m on u.principal_id = M.member_principal_idinner join sys.database_principals g on g.principal_id = M.role_p Rincipal_idorder by g.name,u.name--Grant "securable" permission 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 joins 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 '--assembly permission Query and grant SELECT * FROM Sys.types WHERE is_user_defined=1select * FROM sys . Table_typesselect 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 Ttinner joins Sys.schemas as STT on Stt.schema_ id = Tt.schema_idinner Join sys.database_permissions as PMS on Pms.major_id=tt.user_type_idinner join Sys.database_ Principals as PSP on psp.principal_id = Pms.grantee_principal_idwhere pms.class=6 and pms.minor_id=0 and pms.state = ' G '

Login migration script for SQL Server