[MSSQL] 데이터베이스 복원 시 Login <-> User 매핑 에러 해결

데이터베이스를 다른 서버로 이관 또는 복원 시 

데이터베이스 복구 완료, 사용자 생성까지 완료 되었으나 로그인이 실패하는 경우,

 

Microsoft OLE DB Provider for SQL Server 오류 ‘80004005’
로그인에서 요청한 데이터베이스 “test_user”을(를) 열 수 없습니다. 로그인이 실패했습니다.

 

복구한 사용자와 데이터베이스 내의 사용자가 맞지 않아서 발생한다.

이 에러가 발생할 경우 (패스워드는 틀리지 않았다는 가정 하에) SQL Server 로그인 계정과 데이터베이스의 User가 같다고 데이터베이스에 인지 시켜준다. (사용자 매핑 과정)

 

1. Login <-> User Mapping

EXEC sp_change_users_login 'Report';
EXEC sp_change_users_login 'Auto_Fix', 'test_user', NULL, 'wndrnerp%xp2';
EXEC sp_change_users_login 'Update_One', 'test_user', 'test_user';

 

2. SID를 통해 Login 생성

미러링 구성 시 반드시 필요하며 데이터베이스 User는 이미 생성 되었고 서버의 Login만 생성하면 되는 경우에 사용.

데이터베이스가 Write가 안되는 경우(미러링 구성시 보조서버일 경우)는 해당 데이터베이스의 User정보를 가지고 서버의 Login 을 만들어야 한다.

CREATE LOGIN test_user { WITH {option_list1} | FROM {sources} }
 
{option_list1} ::=
    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
    [ , {option_list2} [ ,... ] ]
 
{option_list2} ::=
    SID = sid
    | DEFAULT_DATABASE = database  
    | DEFAULT_LANGUAGE = language
    | CHECK_EXPIRATION = { ON | OFF}
    | CHECK_POLICY = { ON | OFF}
    | CREDENTIAL = credential_name
 
{sources} ::=
    WINDOWS [ WITH {windows_options} [ ,... ] ]
    | CERTIFICATE certname
    | ASYMMETRIC KEY asym_key_name
 
{windows_options} ::=    
    DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language

 

** SID 조회

SELECT * 
FROM sys.server_principals

위 쿼리로 조회한 SID를 가지고 Login을 생성한다.

CREATE LOGIN [test_user]
 WITH
 PASSWORD = 'test_user',
 SID = 0x740E5B99822C43409C36C7EA7CD5AF14,
 DEFAULT_DATABASE = [master],
 CHECK_POLICY = OFF,
 CHECK_EXPIRATION = OFF