TechNet
Export (0) Print
Expand All

Permissions Hierarchy (Database Engine)

 

Updated: March 23, 2016

Applies To: SQL Server 2016

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

The Database Engine 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. SQL Server regulates the actions of principals on securables by verifying that they have been granted appropriate permissions.

The following illustration shows the relationships among the Database Engine permissions hierarchies.

The permissions system works the same in all versions of SQL Server, SQL Database, SQL Data Warehouse, Analytics Platform System, however some features are not available in all versions. For example, server-level permission cannot be configured in Azure products.

Diagram of Database Engine permissions hierarchies

For a poster sized chart of all Database Engine permissions in pdf format, see http://go.microsoft.com/fwlink/?LinkId=229142.

Permissions can be manipulated with the familiar Transact-SQL 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.

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)

Show:
© 2016 Microsoft