permissions-hierarchy-database-engine.md

title	Permissions Hierarchy (Database Engine) | Microsoft Docs
ms.custom
ms.date	03/23/2016
ms.prod	sql
ms.prod_service	database-engine, sql-database, sql-data-warehouse, pdw
ms.reviewer
ms.technology	security
ms.topic	conceptual
f1_keywords	sql13.swb.server.permissions.f1--May use common.permissions
helpviewer_keywords	security [SQL Server], denying access hierarchies [SQL Server], permissions securables [SQL Server] security [SQL Server], permissions permissions [SQL Server], hierarchy security [SQL Server], granting access
ms.assetid	f6d20a55-ef03-4e14-85f9-009902889866
author	VanMSFT
ms.author	vanto
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Permissions Hierarchy (Database Engine)

[!INCLUDEappliesto-ss-asdb-asdw-pdw-md]

The [!INCLUDEssDE] manages a hierarchical collection of entities that can be secured with permissions. These entities are known as securables. The most prominent securables are servers and databases, but discrete permissions can be set at a much finer level. [!INCLUDEssNoVersion] regulates the actions of principals on securables by verifying that they have been granted appropriate permissions.

The following illustration shows the relationships among the [!INCLUDEssDE] permissions hierarchies.

The permissions system works the same in all versions of [!INCLUDEssNoVersion], [!INCLUDEssSDS], [!INCLUDEssDW], [!INCLUDEssAPS], however some features are not available in all versions. For example, server-level permission cannot be configured in Azure products.

Chart of SQL Server Permissions

For a poster sized chart of all [!INCLUDEssDE] permissions in pdf format, see https://aka.ms/sql-permissions-poster.

Working with Permissions

Permissions can be manipulated with the familiar [!INCLUDEtsql] queries GRANT, DENY, and REVOKE. Information about permissions is visible in the sys.server_permissions and sys.database_permissions catalog views. There is also support for querying permissions information by using built-in functions.

For information about designing a permissions system, see Getting Started with Database Engine Permissions.

See Also

Securing SQL Server
Permissions (Database Engine)
Securables
Principals (Database Engine)
GRANT (Transact-SQL)
REVOKE (Transact-SQL)
DENY (Transact-SQL)
HAS_PERMS_BY_NAME (Transact-SQL)
sys.fn_builtin_permissions (Transact-SQL)
sys.server_permissions (Transact-SQL)
sys.database_permissions (Transact-SQL)