Securables

 

Updated: October 18, 2016

Applies To: SQL Server 2016

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

Securables are the resources to which the SQL Server Database Engine authorization system regulates access. For example, a table is a securable. Some securables can be contained within others, creating nested hierarchies called "scopes" that can themselves be secured. The securable scopes are server, database, and schema.

The server securable scope contains the following securables:

  • Availability group

  • Endpoint

  • Login

  • Server role

  • Database

The database securable scope contains the following securables:

  • Application role

  • Assembly

  • Asymmetric key

  • Certificate

  • Contract

  • Fulltext catalog

  • Fulltext stoplist

  • Message type

  • Remote Service Binding

  • (Database) Role

  • Route

  • Schema

  • Search property list

  • Service

  • Symmetric key

  • User

The schema securable scope contains the following securables:

  • Type

  • XML schema collection

  • Object – The object class has the following members:

    • Aggregate

    • Function

    • Procedure

    • Queue

    • Synonym

    • Table

    • View

    • External Table

The entity that receives permission to a securable is called a principal. The most common principals are logins and database users. Access to securables is controlled by granting or denying permissions, or by adding logins and users to roles which have access. For information about controlling permissions, see GRANT (Transact-SQL), REVOKE (Transact-SQL), DENY (Transact-SQL), sp_addrolemember (Transact-SQL), and sp_droprolemember (Transact-SQL).

System_CAPS_ICON_caution.jpg Caution


The default permissions that are granted to system objects at the time of setup are carefully evaluated against possible threats and need not be altered as part of hardening the SQL Server installation. Any changes to the permissions on the system objects could limit or break the functionality and could potentially leave your SQL Server installation in an unsupported state.

Getting Started with Database Engine Permissions

Securing SQL Server

sys.database_principals (Transact-SQL)

sys.database_role_members (Transact-SQL)

sys.server_principals (Transact-SQL)

sys.server_role_members (Transact-SQL)

sys.sql_logins (Transact-SQL)

Community Additions

ADD
Show: