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

이 항목은 다음에 적용됩니다. 예SQL Server(2008부터)예Azure SQL Database예Azure SQL Data Warehouse 예병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

SQL ServerSQL Server 의 분리된 사용자는 데이터베이스 사용자가 마스터 데이터베이스의 로그인을 기반으로 하지만 해당 로그인이 마스터에 더 이상 존재하지 않는 경우 발생합니다.Orphaned users in SQL ServerSQL Server occur when a database user is based on a login in the master database, but the login no longer exists in master. 이는 로그인이 삭제되었거나 데이터베이스가 로그인이 존재하지 않는 다른 서버로 이동된 경우에 발생할 수 있습니다.This can occur when the login is deleted, or when the database is moved to another server where the login does not exist. 이 항목에서는 분리된 사용자를 찾아서 로그인에 다시 매핑하는 방법을 설명합니다.This topic describes how to find orphaned users, and remap them to logins.

참고

포함된 데이터베이스 사용자를 이동될 수 있는 데이터베이스에 사용하면 분리된 사용자가 발생할 가능성이 감소합니다.Reduce the possibility of orphaned users by using contained database users for databases that might be moved. 자세한 내용은 포함된 데이터베이스 사용자 - 이식 가능한 데이터베이스 만들기를 참조하세요.For more information, see Contained Database Users - Making Your Database Portable.

배경Background

로그인을 기반으로 하는 보안 주체(데이터베이스 사용자 ID)를 사용하여 SQL ServerSQL Server 의 인스턴스에 있는 데이터베이스에 연결하려면 해당 주체가 master 데이터베이스에 유효한 로그인을 가지고 있어야 합니다.To connect to a database on an instance of SQL ServerSQL Server using a security principal (database user identity) based on a login, the principal must have a valid login in the master database. 이 로그인은 주체의 ID를 확인하고 해당 주체가 SQL ServerSQL Server의 인스턴스에 연결할 수 있는지 여부를 확인하는 인증 프로세스에서 사용됩니다.This login is used in the authentication process that verifies the principals identity and determines if the principal is allowed to connect to the instance of SQL ServerSQL Server. 서버 인스턴스의 SQL ServerSQL Server 로그인은 sys.server_principals 카탈로그 뷰 및 sys.sql_logins 호환성 뷰에서 볼 수 있습니다.The SQL ServerSQL Server logins on a server instance are visible in the sys.server_principals catalog view and the sys.sql_logins compatibility view.

SQL ServerSQL Server 로그인은 SQL ServerSQL Server 로그인에 매핑된 "데이터베이스 사용자"를 사용하여 개별 데이터베이스에 액세스합니다. login. 이 규칙에는 세 가지 예외가 있습니다.There are three exceptions to this rule:

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

    포함된 데이터베이스 사용자는 사용자 데이터베이스 수준에서 인증하며 로그인과 연결되지 않습니다.Contained database users authenticate at the user-database level and are not associated with logins. 이 방법은 데이터베이스의 이식성이 개선되고 포함된 데이터베이스 사용자가 분리될 수 없기 때문에 권장됩니다.This is recommended because the databases are more portable and contained database users cannot become orphaned. 그러나 각 데이터베이스에 대해 사용자를 다시 만들어야 합니다.However they must be recreated for each database. 데이터베이스가 많은 환경에서는 이렇게 하는 것이 어렵습니다.This might be impractical in an environment with many databases.

  • 게스트 계정.The guest account.

    데이터베이스에서 사용하도록 설정된 경우, 이 계정은 데이터베이스 사용자에 매핑되지 않은 SQL ServerSQL Server 로그인이 게스트 사용자로 데이터베이스에 액세스할 수 있도록 합니다.When enabled in the database, this account permits SQL ServerSQL Server logins that are not mapped to a database user to enter the database as the guest user. 게스트 계정은 기본적으로 사용하지 않도록 설정되어 있습니다.The guest account is disabled by default.

  • Microsoft Windows 그룹 멤버 자격Microsoft Windows group memberships.

    Windows 사용자로부터 생성된 SQL ServerSQL Server 로그인은 해당 Windows 사용자가 Windows 그룹의 멤버인 동시에 데이터베이스의 사용자인 경우 데이터베이스에 액세스할 수 있습니다.A SQL ServerSQL Server login created from a Windows user can enter a database if the Windows user is a member of a Windows group that is also a user in the database.

    데이터베이스 사용자로의 SQL ServerSQL Server 로그인 매핑에 대한 정보는 데이터베이스 내에 저장됩니다.Information about the mapping of a SQL ServerSQL Server login to a database user is stored within the database. 데이터베이스 사용자 이름과 해당 SQL ServerSQL Server 로그인의 SID가 여기에 포함됩니다.It includes the name of the database user and the SID of the corresponding SQL ServerSQL Server login. 이 데이터베이스 사용자의 사용 권한은 데이터베이스에서 권한 부여에 적용됩니다.The permissions of this database user are applied for authorization in the database.

    해당 SQL ServerSQL Server 로그인이 서버 인스턴스에서 정의되지 않았거나 잘못 정의되어 있는 데이터베이스 사용자(로그인 기반)는 이 인스턴스에 로그인할 수 없습니다.A database user (based on a login) for which the corresponding SQL ServerSQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. 이러한 사용자는 해당 서버 인스턴스에 있는 데이터베이스의 분리된 사용자 라고 합니다.Such a user is said to be an orphaned user of the database on that server instance. 데이터베이스 사용자가 master 인스턴스에 없는 로그인 SID로 매핑되는 경우 사용자가 분리될 수 있습니다.Orphaning can happen if the database user is mapped to a login SID that is not present in the master instance. 로그인이 생성되지 않은 경우 데이터베이스가 복원되거나 SQL ServerSQL Server 의 다른 인스턴스에 연결된 후 데이터베이스 사용자가 분리될 수 있습니다.A database user can become orphaned after a database is restored or attached to a different instance of SQL ServerSQL Server where the login was never created. 또한 데이터베이스 사용자는 해당 SQL ServerSQL Server 로그인이 삭제되면 분리될 수 있습니다.A database user can also become orphaned if the corresponding SQL ServerSQL Server login is dropped. 로그인을 다시 만들더라도 SID가 다르므로 여전히 데이터베이스 사용자가 분리됩니다.Even if the login is recreated, it will have a different SID, so the database user will still be orphaned.

분리된 사용자를 검색하려면To Detect Orphaned Users

SQL ServerSQL Server 및 PDW의 경우For SQL ServerSQL Server and PDW

누락된 SQL ServerSQL Server 인증 로그인을 기준으로 SQL ServerSQL Server 에서 분리된 사용자를 검색하려면 사용자 데이터베이스에서 다음 문을 실행합니다.To detect orphaned users in SQL ServerSQL Server based on missing SQL ServerSQL Server authentication logins, execute the following statement in the user database:

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 authentication_type_desc = 'INSTANCE';  

현재 데이터베이스에 있으며 어떤 SQL ServerSQL Server 로그인에도 연결되지 않은 SQL ServerSQL Server 인증 사용자와 이에 해당되는 SID(보안 ID)가 나열됩니다.The output lists the SQL ServerSQL Server authentication users and corresponding security identifiers (SID) in the current database that are not linked to any SQL ServerSQL Server login.

SQL 데이터베이스 및 SQL 데이터 웨어하우스의 경우For SQL Database and SQL Data Warehouse

SQL 데이터베이스 또는 SQL 데이터 웨어하우스에서는 sys.server_principals 테이블을 사용할 수 없습니다.The sys.server_principals table is not available in SQL Database or SQL Data Warehouse. 이러한 환경에서 분리된 사용자를 확인하려면 다음 단계를 따르세요.Identify orphaned users in those environments with the following steps:

  1. master 데이터베이스에 연결하고 다음 쿼리를 사용하여 로그인의 SID를 선택합니다.Connect to the master database and select the SID's for the logins with the following query:

    SELECT sid 
    FROM sys.sql_logins 
    WHERE type = 'S'; 
    
  2. 사용자 데이터베이스에 연결하고 다음 쿼리를 사용하여 sys.database_principals 테이블의 사용자 SID를 검토합니다.Connect to the user database and review the SID's of the users in the sys.database_principals table, by using the following query:

    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가 있는지 확인합니다.Compare the two lists to determine if there are user SID's in the user database sys.database_principals table which are not matched by login SID's in the master database sql_logins table.

분리된 사용자를 확인하려면To Resolve an Orphaned User

master 데이터베이스에서 CREATE LOGIN 문을 SID 옵션과 함께 사용하고 이전 섹션에서 받은 데이터베이스 사용자의 SID 를 제공하여 누락된 로그인을 다시 만듭니다.In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login, providing the SID of the database user obtained in the previous section:

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

분리된 사용자를 master에 이미 존재하는 로그인에 매핑하려면 사용자 데이터베이스에서 로그인 이름을 지정하여 ALTER USER 문을 실행합니다.To map an orphaned user to a login which already exists in master, execute the ALTER USER statement in the user database, specifying the login name.

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

누락된 로그인을 다시 만들 때 사용자가 제공된 암호를 사용하여 데이터베이스에 액세스할 수 있습니다.When you recreate a missing login, the user can access the database using the password provided. 그런 다음 ALTER LOGIN 문을 사용하여 로그인 계정의 암호를 변경할 수 있습니다.Then the user can alter the password of the login account by using the ALTER LOGIN statement.

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

어떤 로그인도 자신의 암호를 변경할 수 있습니다.Any login can change it's own password. ALTER ANY LOGIN 사용 권한이 있는 로그인으로만 다른 사용자의 로그인 암호를 변경할 수 있습니다.Only logins with the ALTER ANY LOGIN permission can change the password of another user's login. 그러나 sysadmin 역할의 멤버만 sysadmin 역할 멤버의 암호를 수정할 수 있습니다.However, only members of the sysadmin role can modify passwords of sysadmin role members.

사용되지 않는 프로시저 sp_change_users_login 은 분리된 사용자에 대해서도 작동합니다.The deprecated procedure sp_change_users_login also works with orphaned users. sp_change_users_loginSQL 데이터베이스SQL Database을 사용할 수 없습니다.sp_change_users_login cannot be used with SQL 데이터베이스SQL Database.

참고 항목See Also

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