TechNet
Export (0) Print
Expand All

DENY System Object Permissions (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Denies permissions on system objects such as stored procedures, extended stored procedures, functions, and views.

Topic link icon Transact-SQL Syntax Conventions

  
DENY { 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 denied.

principal
Specifies the principal from which the permission is being revoked.

This statement can be used to deny 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 Server. 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 only required when you are specifying catalog views and dynamic management views.

System_CAPS_ICON_caution.jpg Caution


Denying permissions on system objects will cause applications that depend on them to fail. SQL Server Management Studio uses catalog views and may not function as expected if you change the default permissions on catalog views.

Denying permissions on triggers and on columns of system objects is not supported.

Permissions on system objects will be preserved during upgrades of SQL Server.

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 denies EXECUTE permission on xp_cmdshell to public.

DENY EXECUTE ON sys.xp_cmdshell TO public;  
GO  

Transact-SQL Syntax Conventions (Transact-SQL)
sys.database_permissions (Transact-SQL)
GRANT System Object Permissions (Transact-SQL)
REVOKE System Object Permissions (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft