Database Migration Prerequisites-Migrating login Account __ Database

background

One scenario that DBAs often encounter is the migration of databases, migrating databases from a server to a B server. After the database has been migrated, we also need to migrate some of the relevant server-level objects, such as jobs, login accounts, linked servers, and so on. Today the main share of the export login account script

Body

---SQL 2005 is available--the solution is found.  --1. Executes the use master go IF object_id (' Sp_hexadecimal ') on a server is not NULL DROP PROCEDURE sp_hexadecimal to CREATE PROCEDURE Sp_hexadecimal @binvalue varbinary (256), @hexvalue varchar (514) OUTPUT as DECLARE @charvalue varchar (514) DECLA RE @i int DECLARE @length int DECLARE @hexstring char Select @charvalue = ' 0x ' Select @i = 1 Select @length = Dataleng TH (@binvalue) SELECT @hexstring = ' 0123456789ABCDEF ' while (@i <= @length) BEGIN DECLARE @tempint int DECLARE @fir stint 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 I F 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 in T DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbina 
RY varbinary 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 CUR SOR for SELECT p.sid, P.name, P.type, p.is_disabled, P.default_database_name, L.hasaccess, L.denylogin from sys.se Rver_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_nam E, l.hasaccess, l.denylogin from Sys.server_principals p left JOIN sys.syslogins l on (l.name = p.name) WHERE p.t Ype in (' S ', ' G ', ' U ') and p.name = @login_name OPEN login_curs FETCH NEXT from login_curs into @SID_varbinary, @name, @type, @is_disabled, @de
  Faultdb, @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 @tm PSTR = ' * * generated ' + CONVERT (varchar, GETDATE ()) + ' on ' + @ @SERVERNAME + '/' Print @tmpstr print ' while (@ @fetc
    H_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 L Ogin ' + QuoteName (@name) + ' from WINDOWS and default_database = [' + @defaultdb + '] ' end ELSE BEGIN--SQL S Erver Authentication--obtain password and sid SET @PWD_varbinary = CAST (LoginProperty (@name, ' Pass Wordhash ') as varbinary (256)) EXEC Sp_hexAdecimal @PWD_varbinary, @PWD_string out of EXEC sp_hexadecimal @SID_varbinary, @SID_string out--obtain PAS  Sword policy State SELECT @is_policy_checked = case is_policy_checked when 1 THEN ' at ' 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 ' if 0 THEN ' off ' ELSE NULL end ' sys.sql_logins WHERE name = @name SET @tmpstr = ' CREATE LOGIN ' + QuoteName (@name) + ' with PASSWORD = ' + @PWD_string + ' hashed, SID = ' + @SID_string + ', default_database = [' + @def Aultdb + '] ' IF (@is_policy_checked is not NULL) BEGIN SET @tmpstr = @tmpstr + ', check_polic Y = ' + @is_policy_checked end IF (@is_expiration_checked are not NULL) BEGIN SET @tmps TR = @tmpstr + ', check_expiration = ' + @is_expiration_checked end IF (@denylogin = 1) BEGIN--L
  Ogin is denied access    SET @tmpstr = @tmpstr + '; DENY CONNECT SQL to ' + QuoteName (@name) End ELSE IF (@hasaccess = 0) BEGIN--Login exists but does not hav E access SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL to ' + QuoteName (@name) End IF (@is_disabled = 1) BEGIN--The login is disabled SET @t Mpstr = @tmpstr + '; ALTER LOGIN ' + QuoteName (@name) + ' DISABLE ' end PRINT @tmpstr end FETCH NEXT from login_curs into @SID_va Rbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin end Close login_curs deallocate login_curs Retu


 RN 0 Go

--Generate Login account script

EXEC master.. Sp_help_revlogin

Copy script to new server Execute

Summary

There are 2 advantages to using this approach. 1. All required login accounts can be synchronized in bulk

2. Since the SID produced is the same, there is no problem of orphaned accounts. In the alway on environment, we need to adopt this approach. Otherwise, the problem of manually processing orphaned accounts without always on switch is required