stargazer:몽상가가 어제 꾼 꿈

SQL Server의 DB 복구시 로그인 계정 연결 문제

검색 키워드 : How to troubleshoot orphan users in SQL Server databases?


데이터베이스를 복구 할 때, (특히 새로 세팅한 서버에 기존 DB 를 복구하거나 연결하는 등의 경우)
사용자, 스키마 등등 user 와 login 이 불일치하여 당장 사용할 수 없는 경우가 있다.

그때 마다 사용자를 제거했다가 다시 로그인 계정을 생성하는 방식으로 매칭을 시키거나 하는 방법으로 쓸수는 있었는데
다음과 같은 프로시저가 sql 내장 프로시저에 존재한다.

sp_change_users_login 구문

sp_change_users_login [ @Action = ] 'action' 
    [ , [ @UserNamePattern = ] 'user' ] 
    [ , [ @LoginName = ] 'login' ] 
        [ , [ @Password = ] 'password' ]

다음 명령으로 로그인 계정에 연결되지 않은 사용자를 조회할 수 있다 :
EXEC sp_change_users_login 'Report';
또한 다음 명령으로 로그인 계정에 연결되지 않은 사용자를 사용자를 위한 로그인 계정을 생성하고 할당할 수 있다 :

use [database]

CREATE LOGIN [login_id] WITH PASSWORD = [password];
GO

--sp_change_users_login [ @Action = ] 'action'
--    [ , [ @UserNamePattern = ] 'user' ]
--    [ , [ @LoginName = ] 'login' ]
--        [ , [ @Password = ] 'password' ]

--Update_One
-- 현재 데이터베이스에서 지정된 user를 기존 SQL Server login에 연결합니다.
-- user와 login은 지정해야 합니다. password는 NULL이거나 지정하지 않아야 합니다.
 
EXEC sp_change_users_login 'update_one', '[user_id]', '[login_id]';

--exec sp_change_users_login
--    @Action ='auto_fix'
--    ,@UserNamePattern = '[login_id]'
--    ,@LoginName = null
--    ,@Password = '[passwd]'

exec sp_change_users_login  @Action ='report'

더 자세한 내용은 SQL Server 2005 매뉴얼에서 sp_change_users_login 시스템 프로시저 항목에서 읽어보자.