출처 : http://www.sqlworld.pe.kr

SQL Server의 데이터베이스를 다른 서버로 옮기기는 쉽습니다. 항상 문제가 되는 것은 다른 서버 로그인 정보를 옮기는 것입니다.(물론 몇개 안되면 다시 만들면 되지만) Master 데이터베이스를 백업받아 다른 서버에 리스토어 하면 되지만 그리 쉬운 방법은 아닙니다. DTS를 이용해서 로그인 정보를 다른 서버로 옮길 수 있으나 SQL Server 7.0에서는 패스워드를 정확히 옮기지 못하는 걸로 압니다. SQL Server 2000에서는 정확하게 로그인 정보를 옮길 수 있습니다.

이런 이유로 MS에서 SQL Server 7.0 간의 로그인 정보 이동을 위하여 제공하는 스크립트가 있습니다. 이 스크립트를 소개하고자 합니다.


1. 아래 스크립트를 수행하여 시스템 저장프로시져 sp_hexadecimal를 만듭니다.

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(256) OUTPUT
AS
DECLARE @charvalue varchar(255)
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 (255)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (255)

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name

OPEN login_curs
FETCH NEXT FROM login_curs INTO @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 = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END

ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END

END

ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar, ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary, ' + @txtpwd + ')'
PRINT @tmpstr
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @encryptopt = '
END

ELSE BEGIN
-- Null password
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @encryptopt = '
END

IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END

END

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

CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO



2. EXEC master..sp_help_revlogin 를 수행하면 로그인 정보를 만드는 스크립트가 만들어집니다.

3. 이 스크립트를 복사하여 새로운 서버에서 수행합니다.

※ 이 내용는 http://support.microsoft.com/support/kb/articles/Q246/1/33.ASP의 내용을 참고한 것임을 알려드립니다.