다음을 통해 공유

분리된 사용자 문제 해결(SQL Server)

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

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

참고

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

배경

로그인을 기반으로 보안 주체(데이터베이스 사용자 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 로그인이 정의되지 않았거나 서버 인스턴스에서 잘못 정의된 데이터베이스 사용자(로그인 기반)는 인스턴스에 로그인할 수 없습니다. 이러한 사용자는 해당 서버 인스턴스에 있는 데이터베이스의 분리된 사용자라고 합니다. 데이터베이스 사용자가 데이터베이스에 없는 master 로그인 SID 에 매핑되는 경우 분리가 발생할 수 있습니다. 데이터베이스가 로그인이 생성되지 않은 다른 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_principalssql_logins 테이블에 있는지 여부를 확인합니다master.

분리된 사용자 확인

master 데이터베이스에서 누락된 로그인을 다시 만드는 옵션과 함께 SIDCREATE LOGIN 문을 사용합니다. 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 역할 멤버의 암호를 수정할 수 있습니다.

관련 콘텐츠

  • Last updated on