SQL Server 인스턴스 간에 로그인 및 암호 동기화

다음 내용은

SQL Server 2005 인스턴스 간에 로그인 및 암호를 전송하는 방법:
http://support.microsoft.com/kb/918992/  

의 문서 내용중 쿼리 스크립트에 관련된 것인데,. 특히 SQL 의 DB 이전이나 복원시에 요긴하게 쓸수 있는 스크립트 입니다.

어디에 있는줄은 알지만, 필요할때 곧장 찾을려니 찾는데도 시간 걸리네요.. -_-;

해당 쿼리의 핵심은 다음의 패스워드 관련 부분입니다.


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

계정에 관련된 정보는 Master DB의 [sysxlogins] 테이블의 sid 및 password 입니다. 이 부분을 위와 같은 프로시져로 값을 변환하여 각 계정별 목록에 대해서 fetch 로 돌아가면서 대상 SQL 서버에서 실행할 쿼리문을 생성해 줍니다.

사용자 계정의 예를 보면,.

sid: 0x1D499426EEBDF6489CF08E61E7CAE31E
password: 0x01000D311E144B09CFA96A91D6D66EF23C6206FD351ED1AE0793AF8970778E4BBB
57EC1DCEACD8D9426EA6AD4D83

와 같은 형식입니다.


그냥 문서에 있는  쿼리 긁어다가 원본 서버에서 실행후, 마지막으로 sp_help_revlogin 프로시져를 호출해 주면 결과값에 새로운 쿼리 문자열을 출력해 주는데 그것을 대상 서버에서 실행만 하면 됩니다.

-- Login: wssplex
CREATE LOGIN [wssplex] WITH PASSWORD = 0x0100B5CADC010327F0750FE9067CE14E1ECE5A9EB78EEC359175 HASHED, SID = 0x54C8BC424A72FF4CBF6D4B04F49E518B, DEFAULT_DATABASE = [wssplex], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF


다만,. 실제 운영하는 DB와는 상관없는 [sysxlogins] 테이블에 등록된 기존의 Windows 로그인이나 SQL Server 에서 생성한 기본 계정들까지 모두 만들어 내므로 대상서버에서 실행전에 불필요한 계정 쿼리는 삭제후 실행하면 될것 같습니다.^^

그외,. SQL Server 7 이나 SQL Server 2000 그리고 SQL Server 2005 의 버젼간에는 스키마의 차이점 때문에 아래와 같은 kb를 참고해서 해주면 됩니다.


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

SQL Server 7.0 <---> SQL Server 7.0 
SQL Server 7.0  <---> SQL Server 2000
SQL Server 2000 <---> SQL Server 2000


물론, 위와 같은 스크립트는 원래 DB의 테이블이나 뷰, 프로시져등등에 해당 사용자 DB 계정의 소유권등이 설정되어 있을 경우에 효과적입니다.

--> 사용자 DB의 [sysobjects] 테이블의 uid 컬럼을 dbo 로 변경을 해주면 작업이 훨씬 간단해 지지만,. 프로시져나 뷰등의 내부 쿼리문에 특정 사용자를 지정한 경우에는 오류가 날수도 있습니다.

그렇지만,. 모든 스키마를 dbo 로 생성한 경우에는 그냥, 대상서버에서 기존 계정 삭제후, 새로운 계정을 다시 설정해 주는게 훨씬 편하지 않나 싶습니다.^^