SQL Server 2000 로그인 계정 이전 (전달)

SQL Server 2000 다른 머신의 인스턴스간 로그인 계정 정보 전달과 관련하여 아래와 같이 정리하였습니다. KB에 등록된 이 스크립트를 사용할 경우 기본 데이터베이스와 서버 역할이 지정되지 않습니다.

많지 않다면야 상관없지만….. 필요하다면 스크립트를 수정할 필요가 있습니다. 


[시나리오]
SQL Server 2000 이 설치되어 있는 두 머신간의 로그인 및 패스워드 정보를 전송
VLAIGOT1 인스턴스에 있는 계정 정보를 VLAIGOT2 인스턴스로 전송하고자 함 


[환경]
SQL Server 2000 SP4

원본 SQL Server : VLAIGOT1

 


대상 SQL Server : VLAIGOT2 

[작업절차]
1. Source Instance 에서 로그인 계정 정보를 추출하기 위해 아래 스크립트를 실행합니다.

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. 스크립트가 정상적으로 수행되었을 경우 master 데이터베이스에 sp_help_revlogin 저장 프로시저가 생성됩니다.

3. 쿼리 분석기에서 아래와 같이 저장 프로시저를 수행합니다.

Exec master..sp_help_revlogin

4. sp_help_revlogin 저장 프로시저가 실행되면 아래와 같이 원본 SID와 패스워드를 사용하여 로그인을 만드는 로그인 스크립트를 생성합니다.

/* sp_help_revlogin script

** Generated 12 10 2008  3:55PM on VLAIGOT1 */

DECLARE @pwd sysname 

-- Login: bornin74
SET @pwd = CONVERT (varbinary(256),
0x0100E82360059A5D5800A366FE6D040B5411A217E00ED206E2BF23FD717AA0CA92E3898B2AB50B4B
0FCB8443445C)

EXEC master..sp_addlogin 'bornin74', @pwd, @sid = 0x1CFD4C6AA734DA43A4F38C5D4FC06BBD,
@encryptopt = 'skip_encryption'


-- Login: BUILTIN\Administrators
EXEC master..sp_grantlogin 'BUILTIN\Administrators'


-- Login: laigo
SET @pwd = CONVERT (varbinary(256), 0x0100A621DC7DC0C3EED0D19B98CD645A8E5ACBEDB3E01F398E7C13DA741C60F18115ABF3E2
17CB9689B495D5E6AC)

EXEC master..sp_addlogin 'laigo', @pwd, @sid = 0x1DC92325A498C24D985C5AB446CFC0CC, @encryptopt = 'skip_encryption'


-- Login: vinban
SET @pwd = CONVERT (varbinary(256),
0x0100CD24640006A19934E7B5477FE0395E14C92E6CF683459B381E3DBADDC846E12020F93A71035
0374818592DBB)

EXEC master..sp_addlogin 'vinban', @pwd, @sid = 0x98A1B43CA6F87E4B85FCE70273CD5CC7,
@encryptopt = 'skip_encryption'


-- Login: wishy
SET @pwd = CONVERT (varbinary(256),
0x01008D23C56D4E0E383FA72D16CDC60A742DCDCBF50E8A90665F4DC34EB56BD972B643EF8F2
493D5D69F1525F2EF)

EXEC master..sp_addlogin 'wishy', @pwd, @sid = 0xEFA51B0DC7388B4AA97B6FFFE2451B5F,
@encryptopt = 'skip_encryption'

5. Destination Instance 에서 위에서 생성된 (sp_help_revlogin 수행 결과) 스크립트를 수행합니다.

새 로그인을 만들었습니다.

'BUILTIN\Administrators'에 대한 로그인 액세스를 허가했습니다.

서버: 메시지 15025, 수준 16, 상태 1, 프로시저 sp_addlogin, 줄 57
'laigo' 로그인이 이미 있습니다.

새 로그인을 만들었습니다.

새 로그인을 만들었습니다.

6. 정상적으로 정보가 갱신되었는지 새 로그인 계정으로 로그인을 시도하여 확인합니다.
 가.  아래 그림과 같이 vinban 로그인 계정으로 로그인이 되었음을 확인할 수 있습니다.

 

[결과확인]
1. laigo 이름과 동일한 로그인 계정이 대상 인스턴스에 이미 존재하기 때문에 생성되지 않습니다.
2. 생성된 계정은 아래와 같습니다.

3. 이 스크립트에서 로그인 계정의 기본 데이터베이스와 서버 역할 정보는 전송되지 않습니다.


[참고자료]
SQL Server 인스턴스 간에 로그인 및 암호를 전송하는 방법
http://support.microsoft.com/kb/246133/


작성자 : Lai Go / 작성일자 : 2008.12.15