SQL Server- Migrate Logins from One server to another server

There is a Microsoft script which we can run it in the source server and script out logins of the source server and run it in the target server.

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

How to run this script:

  1. Run this T-SQL script in the master
  2. After the two procedures is created in master database, You can run the procedure using the command Exec Sp_help_revlogin
  3. This script will generate the login script with password
  4. Run the loginscript in the target server.
print

Leave a Reply

Your email address will not be published. Required fields are marked *


× 6 = eighteen