sys.fn_builtin_permissions (Transact-SQL)
返回对服务器内置权限层次结构的说明。
语法
sys.fn_builtin_permissions ( [ DEFAULT | NULL ]
| empty_string | '<securable_class>' } )
<securable_class> ::=
APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY
| CERTIFICATE | CONTRACT | DATABASE | ENDPOINT | FULLTEXT CATALOG
| LOGIN | MESSAGE TYPE | OBJECT | REMOTE SERVICE BINDING | ROLE
| ROUTE | SCHEMA | SERVER | SERVICE | SYMMETRIC KEY | TYPE
| USER | XML SCHEMA COLLECTION
参数
DEFAULT
使用 DEFAULT 选项调用该函数时,它将返回内置权限的完整列表。NULL
等效于 DEFAULT。empty_string
等效于 DEFAULT。'<securable_class>'
使用一个安全对象类的名称调用它时,sys.fn_builtin_permissions 将返回应用于该类的所有权限。<securable_class> 是一个需要加引号的字符串文字。nvarchar(60)
返回的表
列名 |
数据类型 |
排序规则 |
说明 |
---|---|---|---|
class_desc |
nvarchar(60) |
服务器的排序规则。 |
对安全对象类的说明。 |
permission_name |
sysname |
服务器的排序规则。 |
权限名称。 |
type |
char(4) |
服务器的排序规则。 |
压缩权限类型代码。请参阅随后的表。 |
covering_permission_name |
sysname |
服务器的排序规则。 |
如果不为 NULL,则这是该类的权限名称(表示该类的其他权限)。 |
parent_class_desc |
nvarchar(60) |
服务器的排序规则。 |
如果不为 NULL,则为包含当前类的父类的名称。 |
parent_covering_permission_name |
sysname |
服务器的排序规则。 |
如果不为 NULL,则为父类的权限名称(表示该类的所有其他权限)。 |
压缩权限类型
权限类型。 |
权限名称 |
适用于安全对象或类 |
---|---|---|
ADBO |
ADMINISTER BULK OPERATIONS |
SERVER |
AL |
ALTER |
APPLICATION ROLE |
AL |
ALTER |
ASSEMBLY |
AL |
ALTER |
ASYMMETRIC KEY |
AL |
ALTER |
CERTIFICATE |
AL |
ALTER |
CONTRACT |
AL |
ALTER |
DATABASE |
AL |
ALTER |
ENDPOINT |
AL |
ALTER |
FULLTEXT CATALOG |
AL |
ALTER |
FULLTEXT STOPLIST |
AL |
ALTER |
LOGIN |
AL |
ALTER |
MESSAGE TYPE |
AL |
ALTER |
OBJECT |
AL |
ALTER |
REMOTE SERVICE BINDING |
AL |
ALTER |
ROLE |
AL |
ALTER |
ROUTE |
AL |
ALTER |
SCHEMA |
AL |
ALTER |
SERVICE |
AL |
ALTER |
SYMMETRIC KEY |
AL |
ALTER |
USER |
AL |
ALTER |
XML SCHEMA COLLECTION |
ALAA |
ALTER ANY SERVER AUDIT |
SERVER |
ALAK |
ALTER ANY ASYMMETRIC KEY |
DATABASE |
ALAR |
ALTER ANY APPLICATION ROLE |
DATABASE |
ALAS |
ALTER ANY ASSEMBLY |
DATABASE |
ALCD |
ALTER ANY CREDENTIAL |
SERVER |
ALCF |
ALTER ANY CERTIFICATE |
DATABASE |
ALCO |
ALTER ANY CONNECTION |
SERVER |
ALDA |
ALTER ANY DATABASE AUDIT |
DATABASE |
ALDB |
ALTER ANY DATABASE |
SERVER |
ALDS |
ALTER ANY DATASPACE |
DATABASE |
ALED |
ALTER ANY DATABASE EVENT NOTIFICATION |
DATABASE |
ALES |
ALTER ANY EVENT NOTIFICATION |
SERVER |
ALFT |
ALTER ANY FULLTEXT CATALOG |
DATABASE |
ALHE |
ALTER ANY ENDPOINT |
SERVER |
ALLG |
ALTER ANY LOGIN |
SERVER |
ALLS |
ALTER ANY LINKED SERVER |
SERVER |
ALMT |
ALTER ANY MESSAGE TYPE |
DATABASE |
ALRL |
ALTER ANY ROLE |
DATABASE |
ALRS |
ALTER RESOURCES |
SERVER |
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 |
ALSS |
ALTER SERVER STATE |
SERVER |
ALST |
ALTER SETTINGS |
SERVER |
ALSV |
ALTER ANY SERVICE |
DATABASE |
ALTG |
ALTER ANY DATABASE DDL TRIGGER |
DATABASE |
ALTR |
ALTER TRACE |
SERVER |
ALUS |
ALTER ANY USER |
DATABASE |
AUTH |
AUTHENTICATE |
DATABASE |
AUTH |
AUTHENTICATE SERVER |
SERVER |
BADB |
BACKUP DATABASE |
DATABASE |
BALO |
BACKUP LOG |
DATABASE |
CL |
CONTROL |
APPLICATION ROLE |
CL |
CONTROL |
ASSEMBLY |
CL |
CONTROL |
ASYMMETRIC KEY |
CL |
CONTROL |
CERTIFICATE |
CL |
CONTROL |
CONTRACT |
CL |
CONTROL |
DATABASE |
CL |
CONTROL |
ENDPOINT |
CL |
CONTROL |
FULLTEXT CATALOG |
CL |
CONTROL |
FULLTEXT STOPLIST |
CL |
CONTROL |
LOGIN |
CL |
CONTROL |
MESSAGE TYPE |
CL |
CONTROL |
OBJECT |
CL |
CONTROL |
REMOTE SERVICE BINDING |
CL |
CONTROL |
ROLE |
CL |
CONTROL |
ROUTE |
CL |
CONTROL |
SCHEMA |
CL |
CONTROL |
SERVICE |
CL |
CONTROL |
SYMMETRIC KEY |
CL |
CONTROL |
TYPE |
CL |
CONTROL |
USER |
CL |
CONTROL |
XML SCHEMA COLLECTION |
CL |
CONTROL SERVER |
SERVER |
CO |
CONNECT |
DATABASE |
CO |
CONNECT |
ENDPOINT |
CORP |
CONNECT REPLICATION |
DATABASE |
COSQ |
CONNECT SQL |
SERVER |
CP |
CHECKPOINT |
DATABASE |
CRAG |
CREATE AGGREGATE |
DATABASE |
CRAK |
CREATE ASYMMETRIC KEY |
DATABASE |
CRAS |
CREATE ASSEMBLY |
DATABASE |
CRCF |
CREATE CERTIFICATE |
DATABASE |
CRDB |
CREATE ANY DATABASE |
SERVER |
CRDB |
CREATE DATABASE |
DATABASE |
CRDE |
CREATE DDL EVENT NOTIFICATION |
SERVER |
CRDF |
CREATE DEFAULT |
DATABASE |
CRED |
CREATE DATABASE DDL EVENT NOTIFICATION |
DATABASE |
CRFN |
CREATE FUNCTION |
DATABASE |
CRFT |
CREATE FULLTEXT CATALOG |
DATABASE |
CRHE |
CREATE ENDPOINT |
SERVER |
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 |
CRTE |
CREATE TRACE EVENT NOTIFICATION |
SERVER |
CRTY |
CREATE TYPE |
DATABASE |
CRVW |
CREATE VIEW |
DATABASE |
CRXS |
CREATE XML SCHEMA COLLECTION |
DATABASE |
DL |
DELETE |
DATABASE |
DL |
DELETE |
OBJECT |
DL |
DELETE |
SCHEMA |
EX |
EXECUTE |
DATABASE |
EX |
EXECUTE |
OBJECT |
EX |
EXECUTE |
SCHEMA |
EX |
EXECUTE |
TYPE |
EX |
EXECUTE |
XML SCHEMA COLLECTION |
IM |
IMPERSONATE |
LOGIN |
IM |
IMPERSONATE |
USER |
IN |
INSERT |
DATABASE |
IN |
INSERT |
OBJECT |
IN |
INSERT |
SCHEMA |
RC |
RECEIVE |
OBJECT |
RF |
REFERENCES |
ASSEMBLY |
RF |
REFERENCES |
ASYMMETRIC KEY |
RF |
REFERENCES |
CERTIFICATE |
RF |
REFERENCES |
CONTRACT |
RF |
REFERENCES |
DATABASE |
RF |
REFERENCES |
FULLTEXT CATALOG |
RF |
REFERENCES |
FULLTEXT STOPLIST |
RF |
REFERENCES |
MESSAGE TYPE |
RF |
REFERENCES |
OBJECT |
RF |
REFERENCES |
SCHEMA |
RF |
REFERENCES |
SYMMETRIC KEY |
RF |
REFERENCES |
TYPE |
RF |
REFERENCES |
XML SCHEMA COLLECTION |
SHDN |
SHUTDOWN |
SERVER |
SL |
SELECT |
DATABASE |
SL |
SELECT |
OBJECT |
SL |
SELECT |
SCHEMA |
SN |
SEND |
SERVICE |
SPLN |
SHOWPLAN |
DATABASE |
SUQN |
SUBSCRIBE QUERY NOTIFICATIONS |
DATABASE |
TO |
TAKE OWNERSHIP |
ASSEMBLY |
TO |
TAKE OWNERSHIP |
ASYMMETRIC KEY |
TO |
TAKE OWNERSHIP |
CERTIFICATE |
TO |
TAKE OWNERSHIP |
CONTRACT |
TO |
TAKE OWNERSHIP |
DATABASE |
TO |
TAKE OWNERSHIP |
ENDPOINT |
TO |
TAKE OWNERSHIP |
FULLTEXT CATALOG |
TO |
TAKE OWNERSHIP |
FULLTEXT STOPLIST |
TO |
TAKE OWNERSHIP |
MESSAGE TYPE |
TO |
TAKE OWNERSHIP |
OBJECT |
TO |
TAKE OWNERSHIP |
REMOTE SERVICE BINDING |
TO |
TAKE OWNERSHIP |
ROLE |
TO |
TAKE OWNERSHIP |
ROUTE |
TO |
TAKE OWNERSHIP |
SCHEMA |
TO |
TAKE OWNERSHIP |
SERVICE |
TO |
TAKE OWNERSHIP |
SYMMETRIC KEY |
TO |
TAKE OWNERSHIP |
TYPE |
TO |
TAKE OWNERSHIP |
XML SCHEMA COLLECTION |
UP |
UPDATE |
DATABASE |
UP |
UPDATE |
OBJECT |
UP |
UPDATE |
SCHEMA |
VW |
VIEW DEFINITION |
APPLICATION ROLE |
VW |
VIEW DEFINITION |
ASSEMBLY |
VW |
VIEW DEFINITION |
ASYMMETRIC KEY |
VW |
VIEW DEFINITION |
CERTIFICATE |
VW |
VIEW DEFINITION |
CONTRACT |
VW |
VIEW DEFINITION |
DATABASE |
VW |
VIEW DEFINITION |
ENDPOINT |
VW |
VIEW DEFINITION |
FULLTEXT CATALOG |
VW |
VIEW DEFINITION |
FULLTEXT STOPLIST |
VW |
VIEW DEFINITION |
LOGIN |
VW |
VIEW DEFINITION |
MESSAGE TYPE |
VW |
VIEW DEFINITION |
OBJECT |
VW |
VIEW DEFINITION |
REMOTE SERVICE BINDING |
VW |
VIEW DEFINITION |
ROLE |
VW |
VIEW DEFINITION |
ROUTE |
VW |
VIEW DEFINITION |
SCHEMA |
VW |
VIEW DEFINITION |
SERVICE |
VW |
VIEW DEFINITION |
SYMMETRIC KEY |
VW |
VIEW DEFINITION |
TYPE |
VW |
VIEW DEFINITION |
USER |
VW |
VIEW DEFINITION |
XML SCHEMA COLLECTION |
VWCT |
VIEW CHANGE TRACKING |
OBJECT |
VWCT |
VIEW CHANGE TRACKING |
SCHEMA |
VWAD |
VIEW ANY DEFINITION |
SERVER |
VWDB |
VIEW ANY DATABASE |
SERVER |
VWDS |
VIEW DATABASE STATE |
DATABASE |
VWSS |
VIEW SERVER STATE |
SERVER |
XA |
EXTERNAL ACCESS ASSEMBLY |
SERVER |
XU |
UNSAFE ASSEMBLY |
SERVER |
注释
sys.fn_builtin_permissions 是一个表值函数,它可以产生预定义权限层次结构的副本。此层次结构包含涵盖权限。DEFAULT 结果集说明了一个定向循环的权限层次结构图形,其根为(类 = SERVER,权限 = CONTROL SERVER)。
sys.fn_builtin_permissions 不接受相关参数。
使用无效类名调用 sys.fn_builtin_permissions 时,将返回一个空集。
权限
要求具有 public 角色的成员身份。
示例
A. 列出所有内置权限
SELECT * FROM sys.fn_builtin_permissions(DEFAULT)
B. 列出可对对称密钥设置的权限
SELECT * FROM sys.fn_builtin_permissions(N'SYMMETRIC KEY')
C. 列出对其拥有 SELECT 权限的类
SELECT * FROM sys.fn_builtin_permissions(DEFAULT)
WHERE permission_name = 'SELECT';
更改历史记录
更新的内容 |
---|
增加了与 FULLTEXT STOPLIST 有关的五个权限。 |
为 SERVER 添加了 ALTER ANY SERVER AUDIT,为 DATABASE 添加了 ALTER ANY DATABASE AUDIT,为 OBJECT 添加了 VIEW CHANGE TRACKING,为 SCHEMA 添加了 VIEW CHANGE TRACKING。 |
删除了针对 ASSEMBLY 的 EXECUTE,它已不存在。 |