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

적용 대상:SQL ServerAzure SQL 데이터베이스Azure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System(PDW)

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

배경

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

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

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

  포함된 데이터베이스 사용자는 사용자 데이터베이스 수준에서 인증하며 로그인과 연결되지 않습니다. 데이터베이스의 이식성이 더 뛰어나고 포함된 데이터베이스 사용자가 분리될 수 없기 때문에 이 방법을 사용하는 것이 좋습니다. 그러나 각 데이터베이스에 대해 다시 만들어야 합니다. 데이터베이스가 많은 환경에서는 비실용적일 수 있습니다.

• 게스트 계정.

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

• Microsoft Windows 그룹 구성원.

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

데이터베이스 사용자에 대한 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(보안 ID)가 나열됩니다.

SQL Database 및 Azure Synapse Analytics의 경우

SQL Database 또는 Azure Synapse Analytics에서는 sys.server_principals 테이이블을 사용할 수 없습니다. 다음 단계를 사용하여 해당 환경에서 분리된 사용자를 식별합니다.

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. 두 목록을 비교하여 사용자 데이터베이스 sys.database_principals 테이블에 master 데이터베이스 sql_logins 테이블의 로그인 SID와 일치하지 않는 사용자 SID가 있는지 확인합니다.

분리된 사용자 해결

master 데이터베이스에서 SID 옵션과 함께 CREATE 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>';  

참고 항목

CREATE LOGIN(Transact-SQL)
ALTER USER(Transact-SQL)
CREATE USER(Transact-SQL)
sys.database_principals(Transact-SQL)
sys.server_principals(Transact-SQL)
sp_change_users_login(Transact-SQL)
sp_addlogin(Transact-SQL)
sp_grantlogin(Transact-SQL)
sp_password(Transact-SQL)
sys.sysusers(Transact-SQL)
sys.sql_loginssys.syslogins(Transact-SQL)