sys.database_permissions(Transact-SQL)sys.database_permissions (Transact-SQL)

이 항목은 다음에 적용됩니다. 예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

데이터베이스에 있는 각 사용 권한이나 열 예외 권한에 대해 행을 반환합니다.Returns a row for every permission or column-exception permission in the database. 열에는 해당 개체 수준 사용 권한과는 다른 모든 사용 권한에 대한 행이 있습니다.For columns, there is a row for every permission that is different from the corresponding object-level permission. 열 사용 권한이 해당 개체 사용 권한과 동일한 경우에 대 한 행이 및 개체의 적용 된 권한입니다.If the column permission is the same as the corresponding object permission, there is no row for it and the permission applied is that of the object.

중요

열 수준 사용 권한은 동일한 엔터티의 개체 수준 사용 권한을 대체합니다.Column-level permissions override object-level permissions on the same entity.

열 이름Column name 데이터 형식Data type DescriptionDescription
클래스class tinyinttinyint 사용 권한이 있는 클래스를 식별합니다.Identifies class on which permission exists.

0 = 데이터베이스0 = Database
1 = 개체 또는 열1 = Object or Column
3 = 스키마3 = Schema
4 = 데이터베이스 보안 주체4 = Database Principal
5 = 어셈블리- 적용할: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017합니다.5 = Assembly - Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.
6 = 형식6 = Type
10 = XML 스키마 컬렉션의 경우-10 = XML Schema Collection -
적용 대상: SQL Server 2008SQL Server 2008 부터 SQL Server 2017SQL Server 2017까지Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.
15 = 메시지 유형- 적용할: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017합니다.15 = Message Type - Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.
16 = 서비스 계약- 적용할: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017합니다.16 = Service Contract - Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.
17 = 서비스- 적용할: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017합니다.17 = Service - Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.
18 = 원격 서비스 바인딩- 적용할: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017합니다.18 = Remote Service Binding - Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.
19 = route- 적용할: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017합니다.19 = Route - Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.
23 = 전체 텍스트 카탈로그- 적용할: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017합니다.23 =Full-Text Catalog - Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.
24 = 대칭 키- 적용할: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017합니다.24 = Symmetric Key - Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.
25 인증서-= 적용할: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017합니다.25 = Certificate - Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.
26 = 비대칭 키- 적용할: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017합니다.26 = Asymmetric Key - Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.
class_descclass_desc nvarchar (60)nvarchar(60) 사용 권한이 있는 클래스에 대한 설명입니다.Description of class on which permission exists.

DATABASEDATABASE

OBJECT_OR_COLUMNOBJECT_OR_COLUMN

SCHEMASCHEMA

DATABASE_PRINCIPALDATABASE_PRINCIPAL

ASSEMBLYASSEMBLY

TYPETYPE

XML_SCHEMA_COLLECTIONXML_SCHEMA_COLLECTION

MESSAGE_TYPEMESSAGE_TYPE

SERVICE_CONTRACTSERVICE_CONTRACT

SERVICESERVICE

REMOTE_SERVICE_BINDINGREMOTE_SERVICE_BINDING

ROUTEROUTE

FULLTEXT_CATALOGFULLTEXT_CATALOG

SYMMETRIC_KEYSSYMMETRIC_KEYS

CERTIFICATECERTIFICATE

ASYMMETRIC_KEYASYMMETRIC_KEY
major_idmajor_id intint 사용 권한이 존재하는 항목의 ID입니다. 이는 클래스에 따라 해석됩니다.ID of thing on which permission exists, interpreted according to class. 일반적으로 major_id 단순히 클래스가 나타내는에 적용 되는 ID의 종류입니다.Usually, the major_id is simply the kind of ID that applies to what the class represents.

0 = 데이터베이스 자체0 = The database itself

> 0 = 사용자 개체에 개체 Id>0 = Object-IDs for user objects

<0 = 시스템 개체에 개체 Id<0 = Object-IDs for system objects
minor_idminor_id intint 사용 권한이 존재하는 항목의 보조 ID입니다. 이는 클래스에 따라 해석됩니다.Secondary-ID of thing on which permission exists, interpreted according to class. 대개는 major_id 개체의 클래스에 사용할 수 없는 하위 범주 있기 때문에 0이 됩니다.Often, the major_id is zero, because there is no subcategory available for the class of object. 그렇지 것은 테이블의 열 ID입니다.Otherwise, it is the Column-ID of a table.
grantee_principal_idgrantee_principal_id intint 사용 권한이 부여된 데이터베이스 보안 주체 ID입니다.Database principal ID to which the permissions are granted.
grantor_principal_idgrantor_principal_id intint 사용 권한 부여자의 데이터베이스 보안 주체 ID입니다.Database principal ID of the grantor of these permissions.
유형type char(4)char(4) 데이터베이스 사용 권한의 유형입니다.Database permission type. 사용 권한 유형 목록은 다음 표를 참조하세요.For a list of permission types, see the next table.
permission_namepermission_name nvarchar (128)nvarchar(128) 사용 권한 이름입니다.Permission name.
상태state char(1)char(1) 사용 권한 상태입니다.Permission state:

D = 거부D = Deny

R = 취소R = Revoke

G = 허용G = Grant

W = Grant 옵션을 사용하여 허용W = Grant With Grant Option
state_descstate_desc nvarchar (60)nvarchar(60) 사용 권한 상태에 대한 설명입니다.Description of permission state:

DENYDENY

REVOKEREVOKE

GRANTGRANT

GRANT_WITH_GRANT_OPTIONGRANT_WITH_GRANT_OPTION

데이터베이스 권한Database Permissions

사용 권한 다음 형식을 사용할 수 있습니다.The following types of permissions are possible.

사용 권한 유형Permission type 사용 권한 이름Permission name 보안 개체에 적용되는 항목Applies to securable
AADSAADS ALTER ANY DATABASE EVENT SESSIONALTER ANY DATABASE EVENT SESSION DATABASEDATABASE
AAMKAAMK ALTER ANY MASKALTER ANY MASK DATABASEDATABASE
AEDSAEDS ALTER ANY EXTERNAL DATA SOURCEALTER ANY EXTERNAL DATA SOURCE DATABASEDATABASE
AEFFAEFF ALTER ANY EXTERNAL FILE FORMATALTER ANY EXTERNAL FILE FORMAT DATABASEDATABASE
ALAL ALTERALTER APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, USER, XML SCHEMA COLLECTIONAPPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, USER, XML SCHEMA COLLECTION
ALAKALAK ALTER ANY ASYMMETRIC KEYALTER ANY ASYMMETRIC KEY DATABASEDATABASE
ALARALAR ALTER ANY APPLICATION ROLEALTER ANY APPLICATION ROLE DATABASEDATABASE
ALASALAS ALTER ANY ASSEMBLYALTER ANY ASSEMBLY DATABASEDATABASE
ALCFALCF ALTER ANY CERTIFICATEALTER ANY CERTIFICATE DATABASEDATABASE
ALDSALDS ALTER ANY DATASPACEALTER ANY DATASPACE DATABASEDATABASE
ALEDALED ALTER ANY DATABASE EVENT NOTIFICATIONALTER ANY DATABASE EVENT NOTIFICATION DATABASEDATABASE
ALFTALFT ALTER ANY FULLTEXT CATALOGALTER ANY FULLTEXT CATALOG DATABASEDATABASE
ALMTALMT ALTER ANY MESSAGE TYPEALTER ANY MESSAGE TYPE DATABASEDATABASE
ALRLALRL ALTER ANY ROLEALTER ANY ROLE DATABASEDATABASE
ALRTALRT ALTER ANY ROUTEALTER ANY ROUTE DATABASEDATABASE
ALSBALSB ALTER ANY REMOTE SERVICE BINDINGALTER ANY REMOTE SERVICE BINDING DATABASEDATABASE
ALSCALSC ALTER ANY CONTRACTALTER ANY CONTRACT DATABASEDATABASE
ALSKALSK ALTER ANY SYMMETRIC KEYALTER ANY SYMMETRIC KEY DATABASEDATABASE
ALSMALSM ALTER ANY SCHEMAALTER ANY SCHEMA DATABASEDATABASE
ALSVALSV ALTER ANY SERVICEALTER ANY SERVICE DATABASEDATABASE
ALTGALTG ALTER ANY DATABASE DDL TRIGGERALTER ANY DATABASE DDL TRIGGER DATABASEDATABASE
ALUSALUS ALTER ANY USERALTER ANY USER DATABASEDATABASE
AUTHAUTH AUTHENTICATEAUTHENTICATE DATABASEDATABASE
BADBBADB BACKUP DATABASEBACKUP DATABASE DATABASEDATABASE
BALOBALO BACKUP LOGBACKUP LOG DATABASEDATABASE
CLCL CONTROLCONTROL APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTIONAPPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION
COCO CONNECTCONNECT DATABASEDATABASE
CORPCORP CONNECT REPLICATIONCONNECT REPLICATION DATABASEDATABASE
CPCP CHECKPOINTCHECKPOINT DATABASEDATABASE
CRAGCRAG CREATE AGGREGATECREATE AGGREGATE DATABASEDATABASE
CRAKCRAK CREATE ASYMMETRIC KEYCREATE ASYMMETRIC KEY DATABASEDATABASE
CRASCRAS CREATE ASSEMBLYCREATE ASSEMBLY DATABASEDATABASE
CRCFCRCF CREATE CERTIFICATECREATE CERTIFICATE DATABASEDATABASE
CRDBCRDB CREATE DATABASECREATE DATABASE DATABASEDATABASE
CRDFCRDF CREATE DEFAULTCREATE DEFAULT DATABASEDATABASE
CREDCRED CREATE DATABASE DDL EVENT NOTIFICATIONCREATE DATABASE DDL EVENT NOTIFICATION DATABASEDATABASE
CRFNCRFN CREATE FUNCTIONCREATE FUNCTION DATABASEDATABASE
CRFTCRFT CREATE FULLTEXT CATALOGCREATE FULLTEXT CATALOG DATABASEDATABASE
CRMTCRMT CREATE MESSAGE TYPECREATE MESSAGE TYPE DATABASEDATABASE
CRPRCRPR CREATE PROCEDURECREATE PROCEDURE DATABASEDATABASE
CRQUCRQU CREATE QUEUECREATE QUEUE DATABASEDATABASE
CRRLCRRL CREATE ROLECREATE ROLE DATABASEDATABASE
CRRTCRRT CREATE ROUTECREATE ROUTE DATABASEDATABASE
CRRUCRRU CREATE RULECREATE RULE DATABASEDATABASE
CRSBCRSB CREATE REMOTE SERVICE BINDINGCREATE REMOTE SERVICE BINDING DATABASEDATABASE
CRSCCRSC CREATE CONTRACTCREATE CONTRACT DATABASEDATABASE
CRSKCRSK CREATE SYMMETRIC KEYCREATE SYMMETRIC KEY DATABASEDATABASE
CRSMCRSM CREATE SCHEMACREATE SCHEMA DATABASEDATABASE
CRSNCRSN CREATE SYNONYMCREATE SYNONYM DATABASEDATABASE
CRSOCRSO 적용 대상: SQL Server 2012SQL Server 2012 부터 SQL Server 2017SQL Server 2017까지Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017.

CREATE SEQUENCECREATE SEQUENCE
DATABASEDATABASE
CRSVCRSV CREATE SERVICECREATE SERVICE DATABASEDATABASE
CRTBCRTB CREATE TABLECREATE TABLE DATABASEDATABASE
CRTYCRTY CREATE TYPECREATE TYPE DATABASEDATABASE
CRVWCRVW CREATE VIEWCREATE VIEW DATABASEDATABASE
CRXSCRXS 적용 대상: SQL Server 2008SQL Server 2008 부터 SQL Server 2017SQL Server 2017까지Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

CREATE XML SCHEMA COLLECTIONCREATE XML SCHEMA COLLECTION
DATABASEDATABASE
DABODABO ADMINISTER DATABASE BULK OPERATIONSADMINISTER DATABASE BULK OPERATIONS DATABASEDATABASE
DLDL DELETEDELETE DATABASE, OBJECT, SCHEMADATABASE, OBJECT, SCHEMA
EAESEAES EXECUTE ANY EXTERNAL SCRIPTEXECUTE ANY EXTERNAL SCRIPT DATABASEDATABASE
EXEX CREATE 문을 실행하기 전에EXECUTE ASSEMBLY, DATABASE, OBJECT, SCHEMA, TYPE, XML SCHEMA COLLECTIONASSEMBLY, DATABASE, OBJECT, SCHEMA, TYPE, XML SCHEMA COLLECTION
IMIM IMPERSONATEIMPERSONATE UserUSER
ININ INSERTINSERT DATABASE, OBJECT, SCHEMADATABASE, OBJECT, SCHEMA
RCRC RECEIVERECEIVE OBJECTOBJECT
RFRF REFERENCESREFERENCES ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, SCHEMA, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTIONASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, SCHEMA, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTION
SLSL SELECTSELECT DATABASE, OBJECT, SCHEMADATABASE, OBJECT, SCHEMA
SNSN SENDSEND SERVICESERVICE
SPLNSPLN SHOWPLANSHOWPLAN DATABASEDATABASE
SUQNSUQN SUBSCRIBE QUERY NOTIFICATIONSSUBSCRIBE QUERY NOTIFICATIONS DATABASEDATABASE
TOTO TAKE OWNERSHIPTAKE OWNERSHIP ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTIONASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTION
UPUP UPDATEUPDATE DATABASE, OBJECT, SCHEMADATABASE, OBJECT, SCHEMA
VWVW VIEW DEFINITIONVIEW DEFINITION APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTIONAPPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION
VWCKVWCK VIEW ANY COLUMN ENCRYPTION KEY DEFINITIONVIEW ANY COLUMN ENCRYPTION KEY DEFINITION DATABASEDATABASE
VWCMVWCM VIEW ANY COLUMN MASTER KEY DEFINITIONVIEW ANY COLUMN MASTER KEY DEFINITION DATABASEDATABASE
VWCTVWCT VIEW CHANGE TRACKINGVIEW CHANGE TRACKING TABLE, SCHEMATABLE, SCHEMA
VWDSVWDS VIEW DATABASE STATEVIEW DATABASE STATE DATABASEDATABASE

PermissionsPermissions

모든 사용자는 자산의 권한을 볼 수 있습니다.Any user can see their own permissions. 다른 사용자에 대한 사용 권한을 보려면 로그인할 때 VIEW DEFINITION, ALTER ANY USER, 또는 사용 권한이 필요합니다.To see permissions for other users, requires VIEW DEFINITION, ALTER ANY USER, or any permission on a user. 사용자 정의 역할을 보려면 ALTER ANY ROLE 또는 역할(예: 공용)의 멤버 자격이 필요합니다.To see user-defined roles, requires ALTER ANY ROLE, or membership in the role (such as public).

사용자가 소유하고 있거나 사용 권한을 부여 받은 보안 개체에 대해서만 카탈로그 뷰의 메타데이터를 볼 수 있습니다.The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. 자세한 내용은 Metadata Visibility Configuration을 참조하세요. For more information, see Metadata Visibility Configuration.

Examples

A: 데이터베이스 보안 주체의 모든 사용 권한 나열A: Listing all the permissions of database principals

다음 쿼리는 데이터베이스 보안 주체에 대해 명시적으로 부여되거나 거부된 사용 권한을 나열합니다.The following query lists the permissions explicitly granted or denied to database principals.

중요

고정 데이터베이스 역할의 사용 권한은 sys.database_permissions에 나타나지 않습니다.The permissions of fixed database roles do not appear in sys.database_permissions. 따라서 데이터베이스 보안 주체가 여기에 나열되지 않은 추가 사용 권한을 가질 수 있습니다.Therefore, database principals may have additional permissions not listed here.

SELECT pr.principal_id, pr.name, pr.type_desc,   
    pr.authentication_type_desc, pe.state_desc, pe.permission_name  
FROM sys.database_principals AS pr  
JOIN sys.database_permissions AS pe  
    ON pe.grantee_principal_id = pr.principal_id;  

B:에는 데이터베이스 내의 스키마 개체에 사용 권한 나열B: Listing permissions on schema objects within a database

다음 쿼리는 sys.database_principals 및 sys.database_permissions를 sys.objects 및 sys.schemas에 조인하여 특정 스키마 개체에 부여되거나 거부된 사용 권한을 나열합니다.The following query joins sys.database_principals and sys.database_permissions to sys.objects and sys.schemas to list permissions granted or denied to specific schema objects.

SELECT pr.principal_id, pr.name, pr.type_desc,   
    pr.authentication_type_desc, pe.state_desc,   
    pe.permission_name, s.name + '.' + o.name AS ObjectName  
FROM sys.database_principals AS pr  
JOIN sys.database_permissions AS pe  
    ON pe.grantee_principal_id = pr.principal_id  
JOIN sys.objects AS o  
    ON pe.major_id = o.object_id  
JOIN sys.schemas AS s  
    ON o.schema_id = s.schema_id;  

관련 항목:See Also

Securables Securables
사용 권한 계층(데이터베이스 엔진) Permissions Hierarchy (Database Engine)
보안 카탈로그 뷰(Transact-SQL) Security Catalog Views (Transact-SQL)
카탈로그 뷰(Transact-SQL)Catalog Views (Transact-SQL)