(─━┘_└━─)/ 머리만1톤(Head1Ton)

MS 공식 기술자료 Link
http://support.microsoft.com/kb/918992/en-us

SQL Server 7.0 및 2000 버전에서는 다음 기술자료에 나오는 바와 같이
인스턴스간 로그인 및 암호 전송이 가능합니다.
http://support.microsoft.com/kb/246133/


SQL Server 2005에서도 인스턴스간에 로그인 및 암호를 전송하는 프로시저가 있어서 소개해 드립니다...

GO

 

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

   DROP PROCEDURE sp_hexadecimal

GO

 

CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256),@hexvalue varchar(256) OUTPUT

AS

   DECLARE @charvalue varchar(256)

   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 @xstatus int

   DECLARE @binpwd varbinary (256)

   DECLARE @txtpwd sysname

   DECLARE @tmpstr varchar (256)

   DECLARE @SID_varbinary varbinary(85)

   DECLARE @SID_string varchar(256)

   DECLARE @pwd varchar (256)

 

   IF (@login_name IS NULL)

      DECLARE login_curs CURSOR FOR

         SELECT sid, name, is_disabled, password_hash FROM master.sys.sql_logins

         WHERE type='S' AND name <> 'sa'

   ELSE

      DECLARE login_curs CURSOR FOR

         SELECT sid, name, is_disabled, password_hash FROM master.sys.sql_logins

         WHERE type='S' AND name = @login_name

 

   OPEN login_curs

   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

      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 ''

         PRINT 'DECLARE @pwd sysname'

 

      WHILE (@@fetch_status <> -1)

         BEGIN

            IF (@@fetch_status <> -2)

               BEGIN

                  PRINT ''

                  SET @tmpstr = '-- Login: ' + @name

                  PRINT @tmpstr

 

                  IF (@xstatus & 4) = 4

                     BEGIN -- NT authenticated account/group

                        IF (@xstatus & 1) = 1

                           BEGIN -- NT login is denied access

                              SET @tmpstr='CREATE LOGIN [' + @name +'] FROM WINDOWS'

                              PRINT @tmpstr

                              SET @tmpstr='DENY CONNECT SQL TO[' + @name +']'

                              PRINT @tmpstr

                           END

                        ELSE

                           BEGIN -- NT login has access

                              SET @tmpstr='CREATE LOGIN [' + @name +'] FROM WINDOWS'

                              PRINT @tmpstr

                           END

                     END

                  ELSE

                     BEGIN -- SQL Server authentication

                        IF (@binpwd IS NOT NULL)

                           BEGIN -- Non-null password

                              EXEC sp_hexadecimal @binpwd, @txtpwd OUT

                              EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

                              SET @tmpstr = 'CREATE LOGIN ' +@name+ ' WITH PASSWORD='+ @txtpwd+ ' HASHED,SID=' + @SID_string

                           END

                        ELSE

                           BEGIN

                              -- Null password

                              EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

                              SET @tmpstr = 'CREATE LOGIN ' +@name+ ' WITH PASSWORD='''',SID=' + @SID_string

                           END     

 

                     PRINT @tmpstr

                     END

 

            END

 

            FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

         END

 

   CLOSE login_curs

   DEALLOCATE login_curs

   RETURN 0

GO

 

 

 

[따라하기]

--임의의 로그인 AAA  를생성

CREATE LOGIN AAA WITH PASSWORD='P@ssword'

GO

 

--로그인 생성 스크립트 생성

EXEC sp_help_revlogin

/*

/* sp_help_revlogin script

** Generated 05  3 2006  5:53PM on DSHANNOTE */

   

DECLARE @pwd sysname

   

-- Login: AAA

CREATE LOGIN AAA WITH PASSWORD=0x010032B33F8F5C29F519C7453917FEE2292CDF1068D52158AC51 HASHED,SID=0xE8FE830659D7CC4A8D3AB3EECA79911B

*/

 

DROP LOGIN AAA

GO

 

--생성된 스크립트를 이용하여 AAA 로그인복원

DECLARE @pwd sysname

-- Login: AAA

CREATE LOGIN AAA WITH PASSWORD=0x010032B33F8F5C29F519C7453917FEE2292CDF1068D52158AC51 HASHED,SID=0xE8FE830659D7CC4A8D3AB3EECA79911B

GO

 

--AAA 로 로그인이 되는지 테스트 수행