sys.fn_builtin_permissions (Transact-SQL)
适用于:SQL Server
Azure SQL 数据库 Azure SQL 托管实例
返回服务器内置权限层次结构的说明。 sys.fn_builtin_permissions
只能在 SQL Server 上调用并Azure SQL 数据库,无论它们是否在当前平台上受支持,它都会返回所有权限。 大多数权限适用于所有平台,但有些则不适用。 例如,不能对SQL 数据库授予服务器级别权限。 有关哪些平台支持每个权限的信息,请参阅“权限”(数据库引擎)。
语法
sys.fn_builtin_permissions ( [ DEFAULT | NULL ]
| empty_string | '<securable_class>' } )
<securable_class> ::=
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
参数
[ DEFAULT |NULL ]
使用 DEFAULT 选项(不含引号)调用它时,该函数将返回内置权限的完整列表。
NULL 等效于 DEFAULT。
empty_string
等效于 DEFAULT。
'<securable_class>'
使用一个安全对象类的名称调用时, sys.fn_builtin_permissions
将返回应用于该类的所有权限。 '<securable_class>'
是 nvarchar(60)类型的字符串文本,需要引号。
返回的表
列名称 | 数据类型 | 排序规则 | 说明 |
---|---|---|---|
class_desc | nvarchar(60) | 服务器的排序规则 | 安全对象类的说明。 |
permission_name | nvarchar(60) | 服务器的排序规则 | 权限名称。 |
type | varchar(4) | 服务器的排序规则 | 压缩权限类型代码。 请参阅后面的表。 |
covering_permission_name | nvarchar(60) | 服务器的排序规则 | 如果不为 NULL,则为该类的权限名称(隐含该类的其他权限)。 |
parent_class_desc | nvarchar(60) | 服务器的排序规则 | 如果不为 NULL,则为包含当前类的父类的名称。 |
parent_covering_permission_name | nvarchar(60) | 服务器的排序规则 | 如果不为 NULL,则为父类的权限名称(隐含该类的所有其他权限)。 |
权限类型
权限类型 | 权限名称 | 应用于安全对象或类 |
---|---|---|
AADS | ALTER ANY DATABASE EVENT SESSION 适用于:SQL Server 2014 (12.x) 及 更高版本。 |
DATABASE |
AAES | ALTER ANY EVENT SESSION | SERVER |
AAMK | ALTER ANY MASK 适用于:SQL Server 2016 (13.x) 及更高版本。 |
DATABASE |
ADBO | ADMINISTER BULK OPERATIONS | SERVER |
AEDS | ALTER ANY EXTERNAL DATA SOURCE 适用于:SQL Server 2016 (13.x) 及更高版本。 |
DATABASE |
AEFF | ALTER ANY EXTERNAL FILE FORMAT 适用于:SQL Server 2016 (13.x) 及更高版本。 |
DATABASE |
AL | ALTER | APPLICATION ROLE |
AL | ALTER | ASSEMBLY |
AL | ALTER 适用于:SQL Server 2012 (11.x) 及更高版本。 |
AVAILABILITY GROUP |
AL | ALTER | ASYMMETRIC KEY |
AL | ALTER | CERTIFICATE |
AL | ALTER | CONTRACT |
AL | ALTER | DATABASE |
AL | ALTER 适用于:SQL Server 2017 (14.x) 及更高版本和 Azure SQL 数据库。 |
DATABASE SCOPED CREDENTIAL |
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 | SEARCH PROPERTY LIST |
AL | ALTER 适用于:SQL Server 2012 (11.x) 及更高版本。 |
SERVER ROLE |
AL | ALTER | SERVICE |
AL | ALTER | SYMMETRIC KEY |
AL | ALTER | USER |
AL | ALTER | XML SCHEMA COLLECTION |
ALAA | ALTER ANY SERVER AUDIT | SERVER |
ALAG | ALTER ANY AVAILABILITY GROUP 适用于:SQL Server 2012 (11.x) 及更高版本。 |
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 |
ALCK | ALTER ANY COLUMN ENCRYPTION KEY 适用于:SQL Server 2016 (13.x) 及更高版本。 |
DATABASE |
ALCM | ALTER ANY COLUMN MASTER KEY 适用于:SQL Server 2016 (13.x) 及更高版本。 |
DATABASE |
ALCO | ALTER ANY CONNECTION | SERVER |
ALDA | ALTER ANY DATABASE AUDIT | DATABASE |
ALDB | ALTER ANY DATABASE | SERVER |
ALDC | ALTER ANY DATABASE SCOPED CONFIGURATION 适用于:SQL Server 2016 (13.x) 及更高版本。 |
DATABASE |
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 |
ALSP | 更改任何安全策略 适用于:SQL Server 2016 (13.x) 及更高版本。 |
DATABASE |
ALSR | ALTER ANY SERVER ROLE 适用于:SQL Server 2012 (11.x) 及更高版本。 |
SERVER |
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 |
CADB | CONNECT ANY DATABASE 适用于:SQL Server 2014 (12.x) 及更高版本。 |
SERVER |
CL | CONTROL | APPLICATION ROLE |
CL | CONTROL | ASSEMBLY |
CL | CONTROL | ASYMMETRIC KEY |
CL | CONTROL 适用于:SQL Server 2012 (11.x) 及更高版本。 |
AVAILABILITY GROUP |
CL | CONTROL | CERTIFICATE |
CL | CONTROL | CONTRACT |
CL | CONTROL | DATABASE |
CL | CONTROL 适用于:SQL Server 2017 (14.x) 及更高版本和 Azure SQL 数据库。 |
DATABASE SCOPED CREDENTIAL |
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 | SEARCH PROPERTY LIST |
CL | CONTROL SERVER | SERVER |
CL | CONTROL 适用于:SQL Server 2012 (11.x) 及更高版本。 |
SERVER ROLE |
CL | CONTROL | SERVICE |
CL | CONTROL | SYMMETRIC KEY |
CL | CONTROL | TYPE |
CL | CONTROL | USER |
CL | CONTROL | XML SCHEMA COLLECTION |
CO | CONNECT | DATABASE |
CO | CONNECT | ENDPOINT |
CORP | CONNECT REPLICATION | DATABASE |
COSQ | CONNECT SQL | SERVER |
CP | CHECKPOINT | DATABASE |
CRAC | CREATE AVAILABILITY GROUP 适用于:SQL Server 2012 (11.x) 及更高版本。 |
SERVER |
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 |
CRSO | CREATE SEQUENCE | SCHEMA |
CRSR | CREATE SERVER ROLE 适用于:SQL Server 2012 (11.x) 及更高版本。 |
SERVER |
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 |
DABO | ADMINISTER DATABASE BULK OPERATIONS 适用对象:SQL 数据库。 |
DATABASE |
DL | DELETE | DATABASE |
DL | DELETE | OBJECT |
DL | DELETE | SCHEMA |
EAES | EXECUTE ANY EXTERNAL SCRIPT 适用于:SQL Server 2016 (13.x) 及更高版本。 |
DATABASE |
EX | 执行 | DATABASE |
EX | 执行 | OBJECT |
EX | 执行 | SCHEMA |
EX | 执行 | TYPE |
EX | 执行 | XML SCHEMA COLLECTION |
IAL | IMPERSONATE ANY LOGIN 适用于:SQL Server 2014 (12.x) 及更高版本。 |
SERVER |
IM | IMPERSONATE | LOGIN |
IM | IMPERSONATE | USER |
IN | 插入 | DATABASE |
IN | 插入 | OBJECT |
IN | 插入 | SCHEMA |
KIDC | KILL DATABASE CONNECTION 适用于: Azure SQL 数据库。 |
DATABASE |
RC | RECEIVE | OBJECT |
RF | REFERENCES | ASSEMBLY |
RF | REFERENCES | ASYMMETRIC KEY |
RF | REFERENCES | CERTIFICATE |
RF | REFERENCES | CONTRACT |
RF | REFERENCES | DATABASE |
RF | REFERENCES 适用于:SQL Server 2017 (14.x) 及更高版本和 Azure SQL 数据库。 |
DATABASE SCOPED CREDENTIAL |
RF | REFERENCES | FULLTEXT CATALOG |
RF | REFERENCES | FULLTEXT STOPLIST |
RF | REFERENCES | SEARCH PROPERTY LIST |
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 |
SUS | SELECT ALL USER SECURABLES 适用于:SQL Server 2014 (12.x) 及更高版本。 |
SERVER |
TO | TAKE OWNERSHIP | ASSEMBLY |
TO | TAKE OWNERSHIP | ASYMMETRIC KEY |
TO | TAKE OWNERSHIP 适用于:SQL Server 2012 (11.x) 及更高版本。 |
AVAILABILITY GROUP |
TO | TAKE OWNERSHIP | CERTIFICATE |
TO | TAKE OWNERSHIP | CONTRACT |
TO | TAKE OWNERSHIP | DATABASE |
TO | TAKE OWNERSHIP 适用于:SQL Server 2017 (14.x) 及更高版本和 Azure SQL 数据库。 |
DATABASE SCOPED CREDENTIAL |
TO | TAKE OWNERSHIP | ENDPOINT |
TO | TAKE OWNERSHIP | FULLTEXT CATALOG |
TO | TAKE OWNERSHIP | FULLTEXT STOPLIST |
TO | TAKE OWNERSHIP | SEARCH PROPERTY LIST |
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 适用于:SQL Server 2012 (11.x) 及更高版本。 |
SERVER ROLE |
TO | TAKE OWNERSHIP | SERVICE |
TO | TAKE OWNERSHIP | SYMMETRIC KEY |
TO | TAKE OWNERSHIP | TYPE |
TO | TAKE OWNERSHIP | XML SCHEMA COLLECTION |
UMSK | UNMASK 适用于:SQL Server 2016 (13.x) 及更高版本。 |
DATABASE |
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 适用于:SQL Server 2012 (11.x) 及更高版本。 |
AVAILABILITY GROUP |
VW | VIEW DEFINITION | CERTIFICATE |
VW | VIEW DEFINITION | CONTRACT |
VW | VIEW DEFINITION | DATABASE |
VW | VIEW DEFINITION 适用于:SQL Server 2017 (14.x) 及更高版本和 Azure SQL 数据库。 |
DATABASE SCOPED CREDENTIAL |
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 | SEARCH PROPERTY LIST |
VW | VIEW DEFINITION 适用于:SQL Server 2012 (11.x) 及更高版本。 |
SERVER ROLE |
VW | VIEW DEFINITION | SERVICE |
VW | VIEW DEFINITION | SYMMETRIC KEY |
VW | VIEW DEFINITION | TYPE |
VW | VIEW DEFINITION | USER |
VW | VIEW DEFINITION | XML SCHEMA COLLECTION |
VWAD | VIEW ANY DEFINITION | SERVER |
VWCK | VIEW ANY COLUMN ENCRYPTION KEY DEFINITION 适用于:SQL Server 2016 (13.x) 及更高版本。 |
DATABASE |
VWCM | VIEW ANY COLUMN MASTER KEY DEFINITION 适用于:SQL Server 2016 (13.x) 及更高版本。 |
DATABASE |
VWCT | VIEW CHANGE TRACKING | OBJECT |
VWCT | VIEW CHANGE TRACKING | SCHEMA |
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
结果集描述权限层次结构的有向无环图,根为 (class = SERVER
, permission = CONTROL SERVER
) 。
sys.fn_builtin_permissions
不接受相关参数。
使用无效类名调用 sys.fn_builtin_permissions
时,它返回一个空集。
下图显示了权限以及它们彼此之间的关系。 多次列出了某些更高级别的权限(如 CONTROL SERVER
)。 在本文中,海报太小了,因此无法查看。 你可以下载 PDF 格式的完整数据库引擎权限文章。
权限
要求具有 public 角色的成员身份。
示例
A. 列出所有内置权限
使用 DEFAULT
或空字符串返回所有权限。
SELECT * FROM sys.fn_builtin_permissions(DEFAULT);
SELECT * FROM sys.fn_builtin_permissions('');
B. 列出可在对称密钥上设置的权限
指定一个类以返回该类的所有可能权限。
SELECT * FROM sys.fn_builtin_permissions(N'SYMMETRIC KEY');
°C 列出具有 SELECT 权限的类
SELECT * FROM sys.fn_builtin_permissions(DEFAULT)
WHERE permission_name = 'SELECT';