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 *


5 × = thirty

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>