| title | GRANT System Object Permissions (Transact-SQL) | Microsoft Docs | |||
|---|---|---|---|---|
| ms.custom | ||||
| ms.date | 07/26/2017 | |||
| ms.prod | sql-non-specified | |||
| ms.prod_service | sql-database | |||
| ms.service | ||||
| ms.component | t-sql|statements | |||
| ms.reviewer | ||||
| ms.suite | sql | |||
| ms.technology |
|
|||
| ms.tgt_pltfrm | ||||
| ms.topic | language-reference | |||
| dev_langs |
|
|||
| helpviewer_keywords |
|
|||
| ms.assetid | 9d4e89f4-478f-419a-8b50-b096771e3880 | |||
| caps.latest.revision | 26 | |||
| author | edmacauley | |||
| ms.author | edmaca | |||
| manager | cguyer | |||
| ms.workload | On Demand |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]
Grants permissions on system objects such as system stored procedures, extended stored procedures, functions, and views.
Transact-SQL Syntax Conventions
GRANT { SELECT | EXECUTE } ON [ sys.]system_object TO principal
[ sys.] .
The sys qualifier is required only when you are referring to catalog views and dynamic management views.
system_object
Specifies the object on which permission is being granted.
principal
Specifies the principal to which the permission is being granted.
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 [!INCLUDEssNoVersion]. Each of these system objects exists as a unique record in the resource database of the server (mssqlsystemresource). The resource database is read-only. 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. 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. 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.
Permissions on system objects will be preserved during upgrades of [!INCLUDEssNoVersion].
System objects are visible in the sys.system_objects catalog view. 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
Requires CONTROL SERVER permission.
The following example grants the [!INCLUDEssNoVersion] login Sylvester1 permission to select a view that lists [!INCLUDEssNoVersion] logins. The example then grants the additional permission that is required to view metadata on [!INCLUDEssNoVersion] 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
The following example grants EXECUTE permission on xp_readmail to Sylvester1.
GRANT EXECUTE ON xp_readmail TO Sylvester1;
GO
sys.system_objects (Transact-SQL)
sys.database_permissions (Transact-SQL)
REVOKE System Object Permissions (Transact-SQL)
DENY System Object Permissions (Transact-SQL)