고아 사용자 문제 해결 (SQL Server)

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System(PDW)

사용자가 master 데이터베이스의 로그인을 기반으로 하지만 master에 그 로그인이 더 이상 존재하지 않는 경우, SQL Server에서 고아 상태가 됩니다. 이 문제는 로그인이 삭제되거나 데이터베이스가 로그인이 존재하지 않는 다른 서버로 이동될 때 발생할 수 있습니다. 이 문서에서는 고아 사용자를 찾아 로그인에 다시 매핑하는 방법을 설명합니다.

참고

이동할 수 있는 데이터베이스에 대해 포함된 데이터베이스 사용자를 사용하여 고아 사용자의 가능성을 줄입니다. 자세한 내용은 포함된 데이터베이스를 사용하여 데이터베이스를 이식 가능하게 만들기를 참조하세요.

중요

sp_change_users_login 저장 프로시저는 고아 사용자를 수정하는 데 이전에 사용되었으나, 현재 사용되지 않고 더 이상 지원되지 않습니다. ALTER USER ... WITH LOGIN를 대신 사용하고, 분리된 사용자 확인 섹션에 설명된 대로 수행하십시오. 자세한 내용은 sp_change_users_login(Transact-SQL)를 참조하세요.

배경

로그인을 기반으로 보안 주체(데이터베이스 사용자 ID)를 사용하는 SQL Server 인스턴스의 데이터베이스에 연결하려면 보안 주체에 데이터베이스에 유효한 로그인이 master 있어야 합니다. 이 로그인은 보안 주체의 ID를 확인하고 보안 주체가 SQL Server 인스턴스에 연결할 수 있는지 여부를 결정하는 인증 프로세스에 사용됩니다. 서버 인스턴스의 SQL Server 로그인은 sys.server_principals 카탈로그 뷰와 sys.sql_logins 호환성 뷰에서 볼 수 있습니다.

SQL Server 로그인은 SQL Server 로그인에 매핑된 "데이터베이스 사용자"로 개별 데이터베이스에 액세스합니다. 이 규칙에는 세 가지 예외가 있습니다.

• 포함된 데이터베이스 사용자

  포함된 데이터베이스 사용자는 사용자-데이터베이스 수준에서 인증하며 로그인과 연결되지 않습니다. 이 모델은 데이터베이스의 이식성이 더 높고 포함된 데이터베이스 사용자가 분리될 수 없으므로 권장됩니다. 그러나 각 데이터베이스에 대해 다시 만들어야 합니다. 이 모델은 많은 데이터베이스가 있는 환경에서 비실용적일 수 있습니다.

• 게스트 계정

  데이터베이스에서 사용하도록 설정하면 이 계정은 데이터베이스 사용자에 매핑되지 않은 SQL Server 로그인이 게스트 사용자로 데이터베이스에 액세스할 수 있도록 허용합니다. 게스트 계정은 기본적으로 비활성화됩니다.

• Microsoft Windows 그룹 멤버 자격

  Windows 사용자가 데이터베이스의 사용자인 Windows 그룹의 구성원인 경우 Windows 사용자로부터 만든 SQL Server 로그인은 데이터베이스에 액세스할 수 있습니다.

데이터베이스 사용자에 대한 SQL Server 로그인 매핑에 대한 정보는 데이터베이스에 저장됩니다. 여기에는 데이터베이스 사용자의 이름과 해당 SQL Server 로그인의 보안 식별자(SID)가 포함됩니다. 이 데이터베이스 사용자의 권한은 데이터베이스의 권한 부여에 적용됩니다.

해당 SQL Server 로그인이 정의되지 않았거나 서버 인스턴스에서 잘못 정의된 데이터베이스 사용자(로그인 기반)는 인스턴스에 로그인할 수 없습니다. 이러한 사용자는 해당 서버 인스턴스에서 데이터베이스의 분리된 사용자 입니다. 데이터베이스 사용자가 데이터베이스에 없는 SID 로그인 master 에 매핑되는 경우 분리가 발생할 수 있습니다. 데이터베이스가 로그인이 생성되지 않은 다른 SQL Server 인스턴스에 복원되거나 연결된 후에 데이터베이스 사용자가 분리될 수 있습니다. 데이터베이스 사용자는 해당 SQL Server 로그인이 삭제되면 고아 상태로 남을 수 있습니다. 로그인이 다시 만들어지더라도 다른 SID로그인이 있으므로 데이터베이스 사용자는 여전히 분리됩니다.

고아 상태의 사용자 탐지

SQL Server 및 PDW의 경우

누락된 SQL Server 인증 로그인을 기반으로 SQL Server에서 분리된 사용자를 검색하려면 사용자 데이터베이스에서 다음 문을 실행합니다.

SELECT dp.type_desc, dp.sid, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
    ON dp.sid = sp.sid
WHERE sp.sid IS NULL
    AND dp.authentication_type_desc = 'INSTANCE';

출력에는 SQL Server 로그인에 연결되지 않은 현재 데이터베이스의 SQL Server 인증 사용자 및 해당 SID가 나열됩니다.

Azure SQL Database 및 Azure Synapse Analytics의 경우

sys.server_principals 테이블은 SQL Database 또는 Azure Synapse Analytics에서 사용할 수 없습니다. 이러한 환경에서 분리된 사용자를 식별하려면 다음 단계를 완료합니다.

1. 다음 쿼리를 master 사용하여 데이터베이스에 연결하고 로그인에 대한 SID를 선택합니다.

   SELECT sid
   FROM sys.sql_logins
   WHERE type = 'S';
   

2. 다음 쿼리를 사용하여 사용자 데이터베이스에 연결하고 테이블의 사용자 sys.database_principals SID를 검토합니다.

   SELECT name, sid, principal_id
   FROM sys.database_principals
   WHERE type = 'S'
     AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')
     AND authentication_type_desc = 'INSTANCE';
   

3. 두 목록을 비교하여 데이터베이스 테이블의 로그인 SID와 일치하지 않는 사용자 ID가 사용자 데이터베이스 sys.database_principalsmaster 테이블에 있는지 여부를 확인합니다sql_logins.

고아 사용자 확인

master 데이터베이스에서 누락된 로그인을 다시 만들기 위해 CREATE LOGIN 문을 SID 옵션과 함께 사용합니다. SID 이전 섹션에서 얻은 데이터베이스 사용자의 정보를 제공합니다.

CREATE LOGIN <login_name>
WITH PASSWORD = '<use_a_strong_password_here>',
SID = <SID>;

분리된 사용자를 이미 있는 master로그인에 매핑하려면 사용자 데이터베이스에서 로그인 이름을 지정하여 ALTER USER 문을 실행합니다.

ALTER USER <user_name> WITH Login = <login_name>;

누락된 로그인을 다시 만들 때 사용자는 제공된 암호를 사용하여 데이터베이스에 액세스할 수 있습니다. 그러면 사용자는 다음 문을 사용하여 ALTER LOGIN 로그인 계정의 암호를 변경할 수 있습니다.

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';

중요

어떤 로그인도 자체 암호를 변경할 수 있습니다. ALTER ANY LOGIN 사용 권한이 있는 로그인으로만 다른 사용자의 로그인 암호를 변경할 수 있습니다. 그러나 sysadmin 역할의 멤버만 sysadmin 역할 멤버의 암호를 수정할 수 있습니다.

사용되지 않는 메서드

이전 버전의 SQL Server에서 sp_change_users_login 저장 프로시저는 분리된 사용자를 해결했습니다. 이 절차는 더 이상 사용되지 않으며 이후 버전에서 제거될 수 있습니다. ALTER USER를 대신 사용하세요.

다음 표에서는 일반적인 sp_change_users_login 작업에 해당하는 최신 구문을 보여 줍니다.

사용되지 않는 구문	권장 교체
EXEC sp_change_users_login 'Report'	SELECT dp.name FROM sys.database_principals dp LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid WHERE sp.sid IS NULL AND dp.authentication_type_desc = 'INSTANCE'
EXEC sp_change_users_login 'Auto_Fix', '<user>'	ALTER USER <user> WITH LOGIN = <user>
EXEC sp_change_users_login 'Update_One', '<user>', '<login>'	ALTER USER <user> WITH LOGIN = <login>

자세한 내용은 sp_change_users_login(Transact-SQL)를 참조하세요.

관련 콘텐츠

• CREATE LOGIN(Transact-SQL)
• ALTER USER(Transact-SQL)
• CREATE USER(Transact-SQL)
• sys.database_principals(Transact-SQL)
• sys.server_principals(Transact-SQL)
• sys.sql_logins(Transact-SQL)
• sp_change_users_login(Transact-SQL)
• 포함된 데이터베이스를 사용하여 데이터베이스 이식 가능