SQL Server Security - Principals, Securables and Permissions

Competency Level: 100

Introduction:

Principals, Securables and Permissions are the main entities to understand SQL Server Security. Permissions hierarchy is a good starting point to understand different concept associated with SQL Server security - SQLServerPermissionHierarchy@BOL. There is another much detail version covering different objects associated with that permissions hierarchy is available at SQLServerPermissionsHierarchy@Wiki.

Reading these related entities in following order allows building good understanding of them in a same hierarchical pattern.

Principals:

Entities which can request SQL Server resources -

Principals@BOL

Windows Level Principals

Windows domain login

Windows local logins

SQL Server-Level (Instance Level) Principals

SQL Server Login

Server Role

Related Catalogue Views

sys.server_principals

Database Level Principals

Related Catalogue Views

sys.database_principals

Securables:

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 - Securables@BOLSecurable

Scope: Server

Securable Scope: Database

Securable Scope: Schema

Schema Objects

Permissions:

Every SQL Server securable has associated permissions that can be granted to a principal - Permissions@BOL

Related Catalog Views

sys.server_permissions

sys.database_permissions

Functions

fn_bulitin_permissions

fn_my_permissions

HAS_PERMS_BY_NAME