PRB: 온라인 설명서의 "분리된 사용자 문제 해결" 항목이 불완전하다

현상

다른 서버로 데이터베이스 백업을 복원할 때 분리된 사용자 문제가 발생할 수 있습니다. SQL Server 온라인 설명서의 분리된 사용자 문제 해결 항목에는 이 문제를 해결하는 정확한 단계가 나와 있지 않습니다.

이 문서에서는 분리된 사용자 문제를 해결하는 방법에 대해 설명합니다.

위로 가기 | 피드백 보내기

현재 상태

Microsoft는 본 문서의 시작 부분에 나열한 Microsoft 제품에서 이 문제를 확인했습니다.

위로 가기 | 피드백 보내기

로그인과 사용자라는 용어가 흔히 같은 의미로 사용되지만 사실은 매우 다릅니다. 로그인은 사용자 인증에 사용되고 데이터베이스 사용자 계정은 데이터베이스 액세스와 사용 권한 확인에 사용됩니다. 로그인은 보안 ID(SID)에 의해 사용자와 연결되어 있으며 SQL Server에 액세스하는 데 필요합니다. 특정 로그인이 유효한지 확인하는 프로세스를 "인증"이라고 합니다. 로그인은 SQL Server 데이터베이스 사용자와 연결되어 있어야 합니다. 사용자 계정을 사용하여 데이터베이스에서 수행되는 활동을 제어할 수 있습니다. 특정 로그인에 대한 사용자 계정이 데이터베이스에 없으면 해당 로그인을 사용하고 있는 사용자가 SQL Server에 연결할 수 있는 경우에도 데이터베이스에 액세스할 수 없습니다. 이 경우의 유일한 예외는 데이터베이스에 "guest" 사용자 계정이 포함되어 있을 때입니다. 연결된 사용자 계정이 없는 로그인은 게스트 사용자에 매핑됩니다. 반대로 데이터베이스 사용자가 있지만 연결된 로그인이 없으면 사용자가 SQL Server에 로그인할 수 없습니다.

데이터베이스가 다른 서버로 복원되면 사용자와 사용 권한 집합을 포함하지만 해당하는 로그인이 없거나 로그인이 해당 사용자와 연결되지 않을 수 있습니다. 이것이 "분리된 사용자"가 있는 경우입니다.

분리된 사용자 문제 해결

다른 서버로 데이터베이스 백업을 복원할 때 분리된 사용자 문제가 발생할 수 있습니다. 다음 시나리오에서는 문제와 해결 방법을 보여 줍니다.

master 데이터베이스에 로그인을 추가하고 기본 데이터베이스를 Northwind로 지정합니다.
Use master go sp_addlogin 'test', 'password', 'Northwind'
앞에서 만든 사용자에게 액세스를 부여합니다.
Use Northwind go sp_grantdbaccess 'test'

데이터베이스를 백업합니다.

BACKUP DATABASE Northwind
TO DISK = 'C:\MSSQL\BACKUP\Northwind.bak'

데이터베이스를 다른 SQL Server로 복원합니다.
RESTORE DATABASE Northwind FROM DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
복원된 데이터베이스에 해당하는 로그인이 없는 "test"라는 사용자가 포함되어 있으므로 "test"는 분리된 사용자가 됩니다.
이제 분리된 사용자를 검색하기 위해 다음 코드를 실행합니다.
Use Northwind go sp_change_users_login 'report'
Northwind 데이터베이스의 sysusers 시스템 테이블 항목과 master 데이터베이스의 sysxlogins 시스템 테이블 항목 간에 일치하지 않는 모든 로그인이 출력에 표시됩니다.

분리된 사용자 문제를 해결하는 단계

앞의 단계에서 검색한 분리된 사용자를 대상으로 다음 명령을 실행합니다.
Use Northwind go sp_change_users_login 'update_one', 'test', 'test'
이 명령을 실행하면 서버 로그인 "test"와 Northwind 데이터베이스 사용자 "test"가 다시 연결됩니다. sp_change_users_login 저장 프로시저도 "auto_fix" 매개 변수를 사용하여 모든 분리된 사용자의 업데이트를 수행할 수 있지만 SQL Server가 이름을 기준으로 로그인과 사용자를 일치시키려고 하기 때문에 권장되는 방법이 아닙니다. 대부분의 경우 문제가 생기지 않지만 로그인이 사용자와 제대로 연결되지 않으면 사용자가 잘못된 권한을 가질 수 있습니다.
앞의 단계에서 코드를 실행하면 사용자가 데이터베이스에 액세스할 수 있습니다. 이렇게 되면 사용자가 sp_password 저장 프로시저를 사용하여 암호를 변경할 수 있습니다.
Use master go sp_password NULL, 'ok', 'test'
이 저장 프로시저는 Microsoft Windows NT 보안 계정에 사용할 수 없습니다. Windows NT 네트워크 계정을 통해 SQL Server에 연결하는 사용자는 Windows NT에 의해 인증되므로 사용자의 암호는 Windows NT에서만 변경할 수 있습니다.

sysadmin 역할의 구성원만이 다른 사용자의 로그인 암호를 변경할 수 있습니다.