Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.database_permissions

 

This is a view in the mssqlsystemresource database and their for cannot be found within your normal database server, unless you boot it in single user mode and have an admin connection.

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 will be no row for it and the actual permission used will be that of the object.
 

Column name Data type Description
class tinyint Identifies class on which permission exists.
0 = Database
1 = Object or Column
3 = Schema
4 = Database Principal
5 = Assembly
6 = Type
10 = XML Schema Collection
15 = Message Type
16 = Service Contract
17 = Service
18 = Remote Service Binding
19 = Route
23 = Full-Text Catalog
24 = Symmetric Key
25 = Certificate
26 = Asymmetric Key
class_desc nvarchar(60) Description of class on which permission exists.
DATABASE
OBJECT_OR_COLUMN
SCHEMA
DATABASE_PRINCIPAL
ASSEMBLY
TYPE
XML_SCHEMA_COLLECTION
MESSAGE_TYPE
SERVICE_CONTRACT
SERVICE
REMOTE_SERVICE_BINDING
ROUTE
FULLTEXT_CATALOG
SYMMETRIC_KEY
CERTIFICATE
ASYMMETRIC_KEY
major_id int ID of thing on which permission exists, interpreted according to class. For most, this is simply the kind of ID that applies to what the class represents. Interpretation for nonstandard is as follows:
0 = Always 0
1, 8 = Object-ID
Negative IDs are assigned to system objects.
minor_id int Secondary-ID of thing on which permission exists, interpreted according to class. For most, this is zero. Otherwise, it is the following:
1 = Column-ID if a column. Otherwise, it is 0 if an object.
grantee_principal_id int Database principal ID to which the permissions are granted.
grantor_principal_id int Database principal ID of the grantor of these permissions.
type char(4) Database permission type. For a list of permission types, see the next table.
permission_name nvarchar(128) Permission name.
state char(1) Permission state:
D = Deny
R = Revoke
G = Grant
W = Grant With Grant Option
state_desc nvarchar(60) Description of permission state:
DENY
REVOKE
GRANT
GRANT_WITH_GRANT_OPTION
Permission type Permission name Applies to securable
AL ALTER 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 COLLECTION
ALAK ALTER ANY ASYMMETRIC KEY DATABASE
ALAR ALTER ANY APPLICATION ROLE DATABASE
ALAS ALTER ANY ASSEMBLY DATABASE
ALCF ALTER ANY CERTIFICATE DATABASE
ALDS ALTER ANY DATASPACE DATABASE
ALED ALTER ANY DATABASE EVENT NOTIFICATION DATABASE
ALFT ALTER ANY FULLTEXT CATALOG DATABASE
ALMT ALTER ANY MESSAGE TYPE DATABASE
ALRL ALTER ANY ROLE DATABASE
ALRT ALTER ANY ROUTE DATABASE
ALSB ALTER ANY REMOTE SERVICE BINDING DATABASE
ALSC ALTER ANY CONTRACT DATABASE
ALSK ALTER ANY SYMMETRIC KEY DATABASE
ALSM ALTER ANY SCHEMA DATABASE
ALSV ALTER ANY SERVICE DATABASE
ALTG ALTER ANY DATABASE DDL TRIGGER DATABASE
ALUS ALTER ANY USER DATABASE
AUTH AUTHENTICATE DATABASE
BADB BACKUP DATABASE DATABASE
BALO BACKUP LOG DATABASE
CL CONTROL 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 COLLECTION
CO CONNECT DATABASE
CORP CONNECT REPLICATION DATABASE
CP CHECKPOINT DATABASE
CRAG CREATE AGGREGATE DATABASE
CRAK CREATE ASYMMETRIC KEY DATABASE
CRAS CREATE ASSEMBLY DATABASE
CRCF CREATE CERTIFICATE DATABASE
CRDB CREATE DATABASE DATABASE
CRDF CREATE DEFAULT DATABASE
CRED CREATE DATABASE DDL EVENT NOTIFICATION DATABASE
CRFN CREATE FUNCTION DATABASE
CRFT CREATE FULLTEXT CATALOG DATABASE
CRMT CREATE MESSAGE TYPE DATABASE
CRPR CREATE PROCEDURE DATABASE
CRQU CREATE QUEUE DATABASE
CRRL CREATE ROLE DATABASE
CRRT CREATE ROUTE DATABASE
CRRU CREATE RULE DATABASE
CRSB CREATE REMOTE SERVICE BINDING DATABASE
CRSC CREATE CONTRACT DATABASE
CRSK CREATE SYMMETRIC KEY DATABASE
CRSM CREATE SCHEMA DATABASE
CRSN CREATE SYNONYM DATABASE
CRSV CREATE SERVICE DATABASE
CRTB CREATE TABLE DATABASE
CRTY CREATE TYPE DATABASE
CRVW CREATE VIEW DATABASE
CRXS CREATE XML SCHEMA COLLECTION DATABASE
DL DELETE DATABASE, OBJECT, SCHEMA
EX EXECUTE ASSEMBLY, DATABASE, OBJECT, SCHEMA, TYPE, XML SCHEMA COLLECTION
IM IMPERSONATE USER
IN INSERT DATABASE, OBJECT, SCHEMA
RC RECEIVE OBJECT
RF REFERENCES ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, SCHEMA, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTION
SL SELECT DATABASE, OBJECT, SCHEMA
SN SEND SERVICE
SPLN SHOWPLAN DATABASE
SUQN SUBSCRIBE QUERY NOTIFICATIONS DATABASE
TO TAKE OWNERSHIP ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTION
UP UPDATE DATABASE, OBJECT, SCHEMA
VW VIEW 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 COLLECTION
VWCT VIEW CHANGE TRACKING TABLE, SCHEMA
VWDS VIEW DATABASE STATE DATABASE



Syntax
 CREATE VIEW sys.database_permissions AS
	SELECT p.class,
		class_desc = n.name,
		major_id = p.id,
		minor_id = p.subid,
		grantee_principal_id = p.grantee,
		grantor_principal_id = p.grantor,
		p.type,
		permission_name = permission_name(p.class, p.type),
		p.state,
		state_desc = s.name
	FROM sys.sysprivs p
	LEFT JOIN sys.syspalvalues n ON n.class = 'UNCL' AND n.value = p.class
	LEFT JOIN sys.syspalnames s ON s.class = 'PRST' AND s.value = p.state
	WHERE (p.state <> 'R' OR p.subid <> 0) AND p.class < 100
		AND (has_access('US', p.grantee, p.grantor) = 1 OR has_access('CT', p.class, p.id) = 1)

 
Last revision 2008RTM
See also

  COLUMN_PRIVILEGES (View)
spt_column_privileges_owner_view (View)
spt_column_privileges_set_view (View)
spt_table_privileges_view (View)
sp_changegroup (Procedure)
sp_check_removable_sysusers (Procedure)
sp_column_privileges (Procedure)
sp_column_privileges_rowset (Procedure)
sp_column_privileges_rowset2 (Procedure)
sp_grant_publication_access (Procedure)
sp_helpntgroup (Procedure)
sp_helprotect (Procedure)
sp_MSmakegenerationidentity (Procedure)
sp_MSrepl_FixPALRole (Procedure)
sp_MSrepl_FixTranPALRole (Procedure)
sp_revokedbaccess (Procedure)
sp_verify_proxy_permissions (Procedure)
syspermissions (View)
sysprotects (View)
TABLE_PRIVILEGES (View)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash