효과적인 데이터베이스 엔진 사용 권한 결정Determining Effective Database Engine Permissions

이 항목은 다음에 적용됩니다. 예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 Server 데이터베이스 엔진의 다양한 개체에 대한 사용 권한이 있는 사용자를 결정하는 방법을 설명합니다.This topic describes how to determine who has permissions to various objects in the SQL Server Database Engine. SQL Server는 데이터베이스 엔진에 대한 두 개의 사용 권한 시스템을 구현합니다.SQL Server implements two permission systems for the Database Engine. 고정된 역할의 이전 시스템은 사용 권한이 미리 구성되었습니다.An older system of fixed roles has preconfigured permissions. SQL Server 2005부터 보다 유연하고 정확한 시스템을 사용할 수 있습니다.Beginning with SQL Server 2005 a more flexible and precise system is available. (이 항목의 정보는 SQL Server 2005부터 적용됩니다.(The information in this topic applies to SQL Server, beginning with 2005. SQL Server의 일부 버전에서는 일부 사용 권한 유형을 사용할 수 없습니다.)Some types of permissions are not available in some versions of SQL Server.)

중요
  • 유효 사용 권한은 두 권한 시스템을 집계한 것입니다.The effective permissions are the aggregate of both permission systems.
  • 사용 권한 거부는 권한 부여를 재정의합니다.A denial of permissions overrides a grant of permissions.
  • 사용자가 sysadmin 고정 서버 역할의 멤버인 경우 사용 권한이 더 이상 확인되지 않으므로 거부도 적용되지 않습니다.If a user is a member of the sysadmin fixed server role, permissions are not checked further, so denials will not be enforced.
  • 이전 시스템과 새 시스템에는 유사성이 있습니다.The old system and new system have similarities. 예를 들어 sysadmin 고정 서버 역할의 멤버 자격은 CONTROL SERVER 사용 권한이 있는 것과 비슷합니다.For example, membership in the sysadmin fixed server role is similar to having CONTROL SERVER permission. 하지만 시스템은 동일하지 않습니다.But the systems are not identical. 예를 들어 로그인에 CONTROL SERVER 사용 권한만 있고 저장 프로시저가 sysadmin 고정 서버 역할의 멤버 자격을 확인할 경우에는 권한 검사가 실패합니다.For example, if a login only has the CONTROL SERVER permission, and a stored procedures checks for membership in the sysadmin fixed server role, then the permission check will fail. 그 반대의 경우도 마찬가지입니다.The reverse is also true.

요약Summary

  • 고정 서버 역할 또는 사용자 정의 서버 역할의 멤버 자격에서 서버 수준 사용 권한을 가져올 수 있습니다.Server-level permission can come from membership in the fixed server roles or user-defined server roles. 모든 사용자는 public 고정 서버 역할에 속하며 이 역할에 할당된 사용 권한을 받습니다.Everyone belongs to the public fixed server role and receives any permission assigned there.
  • 서버 수준 사용 권한은 로그인 또는 사용자 정의 서버 역할에 부여하는 사용 권한에서 가져올 수 있습니다.Server-level permissions can come from permission grants to logins or user-defined server roles.
  • 데이터베이스 수준 사용 권한은 각 데이터베이스의 고정 데이터베이스 역할 또는 사용자 정의 데이터베이스 역할의 멤버 자격에서 가져올 수 있습니다.Database-level permission can come from membership in the fixed database roles or user-defined database roles in each database. 모든 사용자는 public 고정 데이터베이스 역할에 속하며 이 역할에 할당된 사용 권한을 받습니다.Everyone belongs to the public fixed database role and receives any permission assigned there.
  • 데이터베이스 수준 사용 권한은 각 데이터베이스의 사용자 또는 사용자 정의 데이터베이스 역할에 부여하는 사용 권한에서 가져올 수 있습니다.Database-level permissions can come from permission grants to users or user-defined database roles in each database.
  • 사용 권한은 guest 로그인 또는 guest 데이터베이스 사용자로부터 받을 수 있습니다(활성화된 경우).Permissions can be received from the guest login or guest database user if enabled. guest 로그인 및 사용자는 기본적으로 비활성화되어 있습니다.The guest login and users are disabled by default.
  • Windows 사용자는 로그인 권한을 가질 수 있는 Windows 그룹의 멤버가 될 수 있습니다.Windows users can be members of Windows groups which can have logins. SQL Server는 Windows 사용자가 연결되고 Windows 그룹의 보안 식별자를 사용하여 Windows 토큰을 제공할 때 Windows 그룹 멤버 자격을 알아냅니다.SQL Server learns of Windows group membership when a Windows user connects and presents a Windows token with the security identifier of a Windows group. Windows 그룹 멤버 자격에 대한 자동 업데이트를 받거나 관리하지 않기 때문에 SQL Server는 Windows 그룹 멤버 자격에서 받는 Windows 사용자의 사용 권한을 확실하게 보고할 수 없습니다.Because SQL Server does not manage or receive automatic updates about Windows group memberships, SQL Server cannot reliably report the permissions of Windows users that are received from Windows group membership.
  • 사용 권한은 응용 프로그램 역할을 전환하고 암호를 제공하여 획득할 수 있습니다.Permissions can be acquired by switching to an application role and providing the password.
  • 사용 권한은 EXECUTE AS 절에 나와 있는 저장 프로시저를 실행하여 획득할 수 있습니다.Permissions can be acquired by executing a stored procedure that includes the EXECUTE AS clause.
  • 사용 권한은 IMPERSONATE 권한이 있는 로그인 또는 사용자에 의해 획득할 수 있습니다.Permissions can be acquired by logins or users with the IMPERSONATE permission.
  • 로컬 컴퓨터 관리자 그룹의 멤버는 언제든지 해당 권한을 sysadmin으로 승격할 수 있습니다.Members of the local computer administrator group can always elevate their privileges to sysadmin. (SQL Database에는 적용되지 않습니다.)(Does not apply to SQL Database.)
  • securityadmin 고정 서버 역할의 멤버는 다양한 해당 권한을 승격할 수 있으며 일부 경우에는 권한을 sysadmin으로 승격할 수 있습니다.Members of the securityadmin fixed server role can elevate many of their privileges and in some cases can elevate the privileges to sysadmin. (SQL Database에는 적용되지 않습니다.)(Does not apply to SQL Database.)
  • SQL Server 관리자는 모든 로그인 및 사용자에 대한 정보를 볼 수 있습니다.SQL Server adminstrators can see information about all logins and users. 권한이 충분하지 않은 사용자는 일반적으로 자신의 고유 ID에 대한 정보만 볼 수 있습니다.Less priviledged users usually see information about only their own identities.

이전 고정 역할 사용 권한 시스템Older Fixed Role Permission System

고정 서버 역할과 고정 데이터베이스 역할에는 변경할 수 없는 미리 구성된 사용 권한이 있습니다.Fixed Server Roles and Fixed Database Roles have preconfigured permissions that cannot be changed. 고정 서버 역할의 멤버를 확인하려면 다음 쿼리를 실행합니다.To determine who is a member of a fixed server role, execute the following query.

참고

서버 수준 사용 권한을 사용할 수 없는 SQL Database 또는 SQL Data Warehouse에는 적용되지 않습니다.Does not apply to SQL Database or SQL Data Warehouse where server level permission are not available. sys.server_principalsis_fixed_role 열이 SQL Server 2012에 추가되었습니다.The is_fixed_role column of sys.server_principals was added in SQL Server 2012. 이전 버전의 SQL Server에는 필요하지 않습니다.It is not needed for older versions of SQL Server.

SELECT SP1.name AS ServerRoleName, 
 isnull (SP2.name, 'No members') AS LoginName   
 FROM sys.server_role_members AS SRM
 RIGHT OUTER JOIN sys.server_principals AS SP1
   ON SRM.role_principal_id = SP1.principal_id
 LEFT OUTER 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;
참고
  • 모든 로그인은 public 역할의 멤버이며 제거할 수 없습니다.All logins are members of the public role and cannot be removed.
  • 이 쿼리는 master 데이터베이스의 테이블을 확인하지만 온-프레미스 제품의 모든 데이터베이스에서 실행할 수 있습니다.This query checks tables in the master database but it can be executed in any database for the on premises product.

고정 데이터베이스 역할의 멤버를 확인하려면 각 데이터베이스에서 다음 쿼리를 실행합니다.To determine who is a member of a fixed database role, execute the following query in each database.

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

각 역할에 부여된 사용 권한을 이해하려면 온라인 설명서에 있는 그림에서 역할 설명을 참조하세요(서버 수준 역할데이터베이스 수준 역할).To understand the permissions that are granted to each role, see the role descriptions at illustrations in Books Online (Server-Level Roles, and Database-Level Roles).

세분화된 최신 사용 권한 시스템Newer Granular Permission System

이 시스템은 매우 유연하여 아주 정확하게 설정하려고 하면 복잡해질 수 있습니다.This system is extremely flexible, which means it can be complicated if the people setting it up want to be very precise. 복잡한 것이 반드시 나쁘지는 않습니다. 금융 기관은 정확한 것이 좋습니다.That's not necessarily bad; I hope my financial institution is precise. 문제를 단순화하려면 역할을 만들고 역할에 사용 권한을 할당한 다음 역할에 사용자 그룹을 추가하는 것이 좋습니다.To simplify matters it helps to create roles, assign permissions to roles, and then add groups of people to the roles. 또한 데이터베이스 개발 팀에서 스키마로 작업을 구분한 후 개별 테이블이나 프로시저 대신 전체 스키마에 역할 사용 권한을 부여하는 경우에는 더 용이합니다.And it's easier if the database development team separates activity by schema and then grants role permissions to a whole schema instead of to individual tables or procedures. 하지만 실제로 복잡하고 비즈니스는 예기치 않은 보안 요구 사항을 필요로 한다는 사실을 가정해야 합니다.But the real world is complex and we have to assume that business needs create unexpected security requirements.

다음 그래픽에서는 권한과 각 권한의 상호 관계를 보여 줍니다.The following graphic shows the permissions and their relationships to each other. 일부 높은 수준의 권한(예: CONTROL SERVER)은 여러 번 나열되어 있습니다.Some of the higher level permissions (such as CONTROL SERVER) are listed many times. 이 항목에서는 포스터가 너무 작아 읽기 어렵습니다.In this topic, the poster is far to small to read. 이미지를 클릭하여 데이터베이스 엔진 사용 권한 포스터를 pdf 형식으로 다운로드합니다.Click the image to download the Database Engine Permissions Poster in pdf format.

[데이터베이스 사용 엔진 권한](http://go.microsoft.com/fwlink/?LinkId=229142)(http://go.microsoft.com/fwlink/?LinkId=229142)

보안 클래스Security Classes

서버 수준, 데이터베이스 수준, 스키마 수준 또는 개체 수준 등에서 사용 권한을 부여할 수 있습니다. 26개의 수준이 있습니다(클래스라고 함).Permissions can be granted at the server-level, the database-level, the schema-level, or the object-level, etc. There are 26 levels (called classes). 클래스의 전체 목록은 APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASE SCOPED CREDENTIAL, ENDPOINT, FULLTEXT CATALOG, FULLTEXT STOPLIST, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SEARCH PROPERTY LIST, SERVER, SERVER ROLE, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION입니다(알파벳순).The complete list of classes in alphabetic order is: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASE SCOPED CREDENTIAL, ENDPOINT, FULLTEXT CATALOG, FULLTEXT STOPLIST, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SEARCH PROPERTY LIST, SERVER, SERVER ROLE, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION. (일부 클래스는 일부 유형의 SQL Server에서 사용할 수 없습니다.) 각 클래스에 대한 전체 정보를 제공하려면 다른 쿼리가 필요합니다.(Some classes are not available on some types of SQL Servers.) To provide full information about each class requires a different query.

보안 주체Principals

사용 권한은 보안 주체에 부여됩니다.Permissions are granted to principals. 서버 역할, 로그인, 데이터베이스 역할 또는 사용자가 보안 주체가 될 수 있습니다.Principals can be server roles, logins, database roles, or users. 로그인은 많은 Windows 사용자를 포함하는 Windows 그룹을 나타낼 수 있습니다.Logins can represent Windows groups that include many Windows users. Windows 그룹은 SQL Server에서 유지 관리하지 않으므로 SQL Server에서 항상 Windows 그룹 멤버를 알 수는 없습니다.Since Windows groups are not maintained by SQL Server, SQL Server does not always know who is a member of a Windows group. Windows 사용자가 SQL Server에 연결하는 경우 로그인 패킷에 사용자에 대한 Windows 그룹 멤버 자격 토큰이 포함됩니다.When a Windows user connects to SQL Server, the login packet contains the Windows group membership tokens for the user.

Windows 사용자가 Windows 그룹을 기반으로 한 로그인을 사용하여 연결하는 경우, 개별 Windows 사용자를 나타내는 사용자 또는 로그인을 만드는 데 SQL Server가 필요한 작업이 있을 수 있습니다.When a Windows user connects using a login based on a Windows group, some activities may require SQL Server to create a login or user to represent the individual Windows user. 예를 들어 Windows 그룹(엔지니어)에 사용자(Mary, Todd, Pat)가 포함되고 엔지니어 그룹에 데이터베이스 사용자 계정이 있습니다.For example, a Windows group (Engineers) contains users (Mary, Todd, Pat) and the Engineers group has a database user account. Mary가 사용 권한이 있고 테이블을 만드는 경우 사용자(Mary)를 테이블의 소유자로 만들 수 있습니다.If Mary has permission and creates a table, a user (Mary) might be created to be the owner of the table. 또는 Todd의 나머지 엔지니어 그룹 사용 권한이 거부되는 경우 사용자 Todd가 사용 권한 거부를 추적하도록 만들어야 합니다.Or if Todd is denied a permission that the rest of the Engineers group has, then the user Todd must be created to track the permission denial.

Windows 사용자(예: 엔지니어 및 관리자)는 둘 이상의 Windows 그룹의 멤버일 수 있습니다.Remember that a Windows user might be a member of more than one Windows group (e.g. both Engineers, and Managers). 엔지니어 로그인에 부여되거나 거부된 사용 권한, 관리자 로그인에 부여되거나 거부된 사용 권한, 사용자에게 개별적으로 부여되거나 거부된 사용 권한 및 사용자가 멤버인 역할에 부여되거나 거부된 사용 권한은 유효 사용 권한에 대해 집계되고 평가됩니다.Permissions granted or denied to the Engineers login, to the Managers login, granted or denied to the user individually, and granted or denied to roles that the user is a member of, will all be aggregated and evaluated to for the effective permissions. HAS_PERMS_BY_NAME 함수는 사용자 또는 로그인에 특정 사용 권한이 있는지 여부를 나타낼 수 있습니다.The HAS_PERMS_BY_NAME function can reveal whether a user or login has a particular permission. 그러나 사용 권한 부여 또는 사용 권한 거부의 원본을 확인하는 명확한 방법은 없습니다.However, there is no obvious way of determining the source of the grant or denial of permission. 사용 권한 목록을 학습하고 시행착오를 거쳐야 합니다.You must study the list of permissions and perhaps experiment using trial and error.

유용한 쿼리Useful Queries

서버 권한Server Permissions

다음 쿼리는 서버 수준에서 부여되거나 거부된 사용 권한 목록을 반환합니다.The following query returns a list of the permissions that have been granted or denied at the server level. 이 쿼리는 master 데이터베이스에서 실행해야 합니다.This query should be executed in the master database.

참고

서버 수준 사용 권한은 SQL Database 또는 SQL Data Warehouse에서 부여하거나 쿼리할 수 없습니다.Server-level permissions cannot be granted or queried on SQL Database or SQL Data Warehouse.

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 OUTER 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;

데이터베이스 권한Database Permissions

다음 쿼리는 데이터베이스 수준에서 부여되거나 거부된 권한 목록을 반환합니다.The following query returns a list of the permissions that have been granted or denied at the database level. 이 쿼리는 각 데이터베이스에서 실행해야 합니다.This query should be executed in each database.

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 OUTER 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;

사용 권한 테이블의 각 클래스를 보안 개체의 해당 클래스에 대한 관련 정보를 제공하는 다른 시스템 뷰에 조인할 수 있습니다.Each class of permission the permission table can be joined to other system views that provide related information about that class of securable. 예를 들어 다음 쿼리는 사용 권한의 영향을 받는 데이터베이스 개체의 이름을 제공합니다.For example, the following query provides the name of the database object that is affected by the permission.

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
 JOIN sys.database_permissions AS pe
   ON pr.principal_id = pe.grantee_principal_id
 JOIN sys.objects AS oj
   ON oj.object_id = pe.major_id
 JOIN sys.schemas AS s
   ON oj.schema_id = s.schema_id
 WHERE class_desc = 'OBJECT_OR_COLUMN';

HAS_PERMS_BY_NAME 함수를 사용하여 특정 사용자(이 경우 TestUser)에게 사용 권한이 있는지 확인합니다.Use the HAS_PERMS_BY_NAME function to determine if a particular user (in this case TestUser) has a permission. 예를 들어For example:

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

구문에 대한 자세한 내용은 HAS_PERMS_BY_NAME을 참조하세요.For the details of the syntax, see HAS_PERMS_BY_NAME.

참고 항목:See Also:

데이터베이스 엔진 사용 권한 시작 Getting Started with Database Engine Permissions
자습서: 데이터베이스 엔진 시작Tutorial: Getting Started with Database Engine