유효한 데이터베이스 엔진 권한 확인

아티클
08/21/2023

적용 대상:SQL Server Azure SQL Database Azure SQL Managed InstanceAzure Synapse Analytics Analytics Platform System(PDW)

이 문서에서는 SQL Server 데이터베이스 엔진의 다양한 개체에 대한 사용 권한이 있는 사용자를 확인하는 방법을 설명합니다. SQL Server는 데이터베이스 엔진에 대한 두 가지 권한 시스템을 구현합니다. 이전의 고정 역할 시스템에는 미리 구성된 권한이 있습니다. SQL Server 2005(9.x)부터 보다 유연하고 정확한 시스템을 사용할 수 있습니다.

참고 항목

이 문서의 정보는 SQL Server 2005(9.x) 이상 버전에 적용됩니다. 일부 유형의 사용 권한은 일부 버전의 SQL Server에서 사용할 수 없습니다.

항상 다음 사항에 유의해야 합니다.

유효 권한은 두 권한 시스템의 집계입니다.
사용 권한 거부는 사용 권한 부여를 재정의합니다.
사용자가 sysadmin 고정 서버 역할의 멤버인 경우 권한은 더 이상 확인되지 않으므로 거부가 적용되지 않습니다.
이전 시스템과 새 시스템에는 유사점이 있습니다. 예를 들어 고정 서버 역할의 sysadmin 멤버 자격은 사용 권한을 갖는 CONTROL SERVER 것과 비슷합니다. 그러나 시스템은 동일하지 않습니다. 예를 들어 로그인에 사용 권한만 CONTROL SERVER 있고 저장 프로시저가 고정 서버 역할의 sysadmin 멤버 자격을 확인하는 경우 사용 권한 검사가 실패합니다. 그 반대의 경우도 마찬가지입니다.

요약

서버 수준 권한은 고정 서버 역할 또는 사용자 정의 서버 역할의 멤버 자격에서 올 수 있습니다. 모든 사용자가 고정 서버 역할에 속 public 하며 할당된 모든 권한을 받습니다.
서버 수준 사용 권한은 로그인 또는 사용자 정의 서버 역할에 부여하는 사용 권한에서 가져올 수 있습니다.
데이터베이스 수준 권한은 각 데이터베이스의 고정 데이터베이스 역할 또는 사용자 정의 데이터베이스 역할의 멤버 자격에서 올 수 있습니다. 모든 사용자가 public 고정 데이터베이스 역할에 속하며 할당된 모든 권한을 받습니다.
데이터베이스 수준 사용 권한은 각 데이터베이스의 사용자 또는 사용자 정의 데이터베이스 역할에 부여하는 사용 권한에서 가져올 수 있습니다.
사용 권한은 guest 로그인 또는 guest 데이터베이스 사용자로부터 받을 수 있습니다(활성화된 경우). guest 로그인 및 사용자는 기본적으로 사용하지 않도록 설정됩니다.
Windows 사용자는 로그인을 가질 수 있는 Windows 그룹의 구성원일 수 있습니다. SQL Server는 Windows 사용자가 Windows 그룹의 보안 식별자를 사용하여 Windows 토큰을 연결하고 표시할 때 Windows 그룹 멤버 자격을 알아봅니다. SQL Server는 Windows 그룹 멤버 자격에 대한 자동 업데이트를 관리하거나 받지 않으므로 SQL Server는 Windows 그룹 멤버 자격에서 받은 Windows 사용자의 권한을 안정적으로 보고할 수 없습니다.
애플리케이션 역할로 전환하고 암호를 제공하여 사용 권한을 획득할 수 있습니다.
사용 권한은 EXECUTE AS 절에 나와 있는 저장 프로시저를 실행하여 획득할 수 있습니다.
사용 권한은 로그인 또는 사용 권한이 있는 사용자가 획득할 IMPERSONATE 수 있습니다.
로컬 컴퓨터 관리자 그룹의 구성원은 항상 권한을 상승시킬 sysadmin수 있습니다. (SQL Database에는 적용되지 않습니다.)
고정 서버 역할의 멤버는 securityadmin 많은 권한을 상승시킬 수 있으며 경우에 따라 권한을 상승시킬 sysadmin수 있습니다. (SQL Database에는 적용되지 않습니다.)
SQL Server 관리자는 모든 로그인 및 사용자에 대한 정보를 볼 수 있습니다. 권한이 적은 사용자는 일반적으로 자신의 ID에 대한 정보만 볼 수 있습니다.

이전 고정 역할 권한 시스템

고정 서버 역할 및 고정 데이터베이스 역할에는 변경할 수 없는 미리 구성된 권한이 있습니다. 고정 서버 역할의 멤버를 확인하려면 다음 쿼리를 실행합니다.

참고 항목

서버 수준 사용 권한을 사용할 수 없는 SQL Database 또는 Azure Synapse Analytics에는 적용되지 않습니다. 열 sys.server_principals 이 is_fixed_role SQL Server 2012(11.x)에 추가되었습니다. 이전 버전의 SQL Server에는 필요하지 않습니다.

SELECT SP1.name AS ServerRoleName,
    ISNULL(SP2.name, 'No members') AS LoginName
FROM sys.server_role_members AS SRM
RIGHT JOIN sys.server_principals AS SP1
    ON SRM.role_principal_id = SP1.principal_id
LEFT JOIN sys.server_principals AS SP2
    ON SRM.member_principal_id = SP2.principal_id
WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008
ORDER BY SP1.name;

참고 항목

모든 로그인은 공용 역할의 멤버이며 제거할 수 없습니다. 쿼리는 데이터베이스의 master 테이블을 검사하지만 온-프레미스 제품에 대한 모든 데이터베이스에서 실행할 수 있습니다.

고정 데이터베이스 역할의 멤버를 확인하려면 각 데이터베이스에서 다음 쿼리를 실행합니다.

SELECT DP1.name AS DatabaseRoleName,
    ISNULL(DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.is_fixed_role = 1
ORDER BY DP1.name;

각 역할에 부여된 사용 권한을 이해하려면 온라인 설명서(서버 수준 역할 및 데이터베이스 수준 역할)의 그림에서 역할 설명을 참조하세요.

유용한 쿼리

서버 권한

다음 쿼리는 서버 수준에서 부여되거나 거부된 사용 권한 목록을 반환합니다. 이 쿼리는 데이터베이스에서 master 실행되어야 합니다.

참고 항목

SQL Database 또는 Azure Synapse Analytics에서 서버 수준 권한을 부여하거나 쿼리할 수 없습니다.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.server_principals AS pr
LEFT JOIN sys.server_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
ORDER BY pr.name,
    type_desc;

데이터베이스 사용 권한

다음 쿼리는 데이터베이스 수준에서 부여되거나 거부된 사용 권한 목록을 반환합니다. 이 쿼리는 각 데이터베이스에서 실행해야 합니다.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.database_principals AS pr
LEFT JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0
ORDER BY pr.name,
    type_desc;

사용 권한 테이블의 각 클래스를 보안 개체의 해당 클래스에 대한 관련 정보를 제공하는 다른 시스템 뷰에 조인할 수 있습니다. 예를 들어 다음 쿼리는 사용 권한의 영향을 받는 데이터베이스 개체의 이름을 제공합니다.

SELECT pr.type_desc,
    pr.name,
    pe.state_desc,
    pe.permission_name,
    s.name + '.' + oj.name AS OBJECT,
    major_id
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
INNER JOIN sys.objects AS oj
    ON oj.object_id = pe.major_id
INNER JOIN sys.schemas AS s
    ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';

함수를 HAS_PERMS_BY_NAME 사용하여 특정 사용자(이 경우 TestUser)에 사용 권한이 있는지 확인합니다. 예:

EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;

구문에 대한 자세한 내용은 HAS_PERMS_BY_NAME 참조하세요.

유효한 데이터베이스 엔진 권한 확인

요약

이전 고정 역할 권한 시스템

최신 세분화된 권한 시스템

보안 클래스

보안 주체

유용한 쿼리

서버 권한

데이터베이스 사용 권한

다음 단계

피드백

피드백

추가 리소스