| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | dev_langs | |||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
REVOKE System Object Permissions (Transact-SQL) |
REVOKE System Object Permissions (Transact-SQL) |
VanMSFT |
vanto |
06/10/2016 |
sql |
t-sql |
reference |
|
|
[!INCLUDE SQL Server]
Revokes permissions on system objects such as stored procedures, extended stored procedures, functions, and views from a principal.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
REVOKE { SELECT | EXECUTE } ON [sys.]system_object FROM 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 revoked.
principal
Specifies the principal from which the permission is being revoked.
This statement can be used to revoke 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 (mssqlsystemresource). The resource database is read-only. A link to the object is exposed as a record in the sys schema of every database.
Default name resolution resolves unqualified procedure names to the resource database. Therefore, the sys. qualifier is required only when you are specifying catalog views and dynamic management views.
Caution
Revoking permissions on system objects will cause applications that depend on them to fail. [!INCLUDEssManStudioFull] uses catalog views and may not function as expected if you change the default permissions on catalog views.
Revoking 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.
Requires CONTROL SERVER permission.
The following example revokes EXECUTE permission on sp_addlinkedserver from public.
REVOKE EXECUTE ON sys.sp_addlinkedserver FROM public;
GO sys.system_objects (Transact-SQL)
sys.database_permissions (Transact-SQL)
GRANT System Object Permissions (Transact-SQL)
DENY System Object Permissions (Transact-SQL)