GRANT 系统对象权限 (Transact-SQL)GRANT System Object Permissions (Transact-SQL)

适用对象: 是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

授予对系统对象(例如,系统存储过程、扩展存储过程、函数以及视图)的权限。Grants permissions on system objects such as system stored procedures, extended stored procedures, functions, and views.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
GRANT { SELECT | EXECUTE } ON [ sys.]system_object TO principal   

参数Arguments

[ sys.] .[ sys.] .
只有在引用目录视图和动态管理视图时才需要 sys 限定符。The sys qualifier is required only when you are referring to catalog views and dynamic management views.

system_object system_object
指定要对其授予权限的对象。Specifies the object on which permission is being granted.

principal principal
指定要向其授予权限的主体。Specifies the principal to which the permission is being granted.

RemarksRemarks

可使用该语句授予对 SQL ServerSQL Server 安装的特定存储过程、扩展存储过程、表值函数、标量函数、视图、目录视图、兼容性视图、INFORMATION_SCHEMA 视图、动态管理视图以及系统表的权限。This statement can be used to grant permissions on certain stored procedures, extended stored procedures, table-valued functions, scalar functions, views, catalog views, compatibility views, INFORMATION_SCHEMA views, dynamic management views, and system tables that are installed by SQL ServerSQL Server. 上述每个系统对象都作为服务器的资源数据库 (mssqlsystemresource) 中的唯一记录而存在。Each of these system objects exists as a unique record in the resource database of the server (mssqlsystemresource). 该资源数据库为只读。The resource database is read-only. 指向对象的链接作为各数据库的 sys 架构中的一条记录显示。A link to the object is exposed as a record in the sys schema of every database. 可以授予、拒绝和撤消执行或选择系统对象的权限。Permission to execute or select a system object can be granted, denied, and revoked.

授予执行或选择对象的权限不一定会提供使用该对象所需的所有权限。Granting permission to execute or select an object does not necessarily convey all the permissions required to use the object. 多数对象执行的操作都需要其他权限。Most objects perform operations for which additional permissions are required. 例如,被授予对 sp_addlinkedserver 的 EXECUTE 权限的用户无法创建链接服务器,除非该用户也是 sysadmin 固定服务器角色的成员。For example, a user that is granted EXECUTE permission on sp_addlinkedserver cannot create a linked server unless the user is also a member of the sysadmin fixed server role.

默认名称解析将解析资源数据库的非限定过程名称。Default name resolution resolves unqualified procedure names to the resource database. 因此,仅当指定目录视图和动态管理视图时,才需要 sys 限定符。Therefore, the sys qualifier is only required when you are specifying catalog views and dynamic management views.

不支持授予对触发器以及对系统对象列的权限。Granting permissions on triggers and on columns of system objects is not supported.

SQL ServerSQL Server 升级期间,对系统对象的权限将予以保留。Permissions on system objects will be preserved during upgrades of SQL ServerSQL Server.

sys.system_objects 目录视图中可以查看系统对象。System objects are visible in the sys.system_objects catalog view. 在 master 数据库中的 sys.database_permissions 目录视图中可以查看对系统对象的权限。The permissions on system objects are visible in the sys.database_permissions catalog view in the master database.

下面的查询将返回有关系统对象的权限的信息:The following query returns information about permissions of system objects:

SELECT * FROM master.sys.database_permissions AS dp   
    JOIN sys.system_objects AS so  
    ON dp.major_id = so.object_id  
    WHERE dp.class = 1 AND so.parent_object_id = 0 ;  
GO  

权限Permissions

需要 CONTROL SERVER 权限。Requires CONTROL SERVER permission.

示例Examples

A.A. 授予对视图的 SELECT 权限Granting SELECT permission on a view

以下示例授予 SQL ServerSQL Server 登录名 Sylvester1 权限,以选择列出 SQL ServerSQL Server 登录名的视图。The following example grants the SQL ServerSQL Server login Sylvester1 permission to select a view that lists SQL ServerSQL Server logins. 然后,本例授予查看不属于该用户的 SQL ServerSQL Server 登录名的元数据所需的其他权限。The example then grants the additional permission that is required to view metadata on SQL ServerSQL Server logins that are not owned by the user.

USE AdventureWorks2012;  
GRANT SELECT ON sys.sql_logins TO Sylvester1;  
GRANT VIEW SERVER STATE to Sylvester1;  
GO  

B.B. 授予对扩展存储过程的 EXECUTE 权限Granting EXECUTE permission on an extended stored procedure

以下示例向 EXECUTE 授予了对 xp_readmailSylvester1 权限。The following example grants EXECUTE permission on xp_readmail to Sylvester1.

GRANT EXECUTE ON xp_readmail TO Sylvester1;  
GO  

另请参阅See Also

sys.system_objects (Transact-SQL) sys.system_objects (Transact-SQL)
sys.database_permissions (Transact-SQL) sys.database_permissions (Transact-SQL)
REVOKE 系统对象权限 (Transact-SQL) REVOKE System Object Permissions (Transact-SQL)
DENY 系统对象权限 (Transact-SQL)DENY System Object Permissions (Transact-SQL)