Securables - Securables are the SQL Server resources that permissions can be granted to.
| Server | Database | Schema |
| Logins | User | Table |
| EndPoints | Role | View |
| Databases | Application role | Function |
| Assembly | Procedure | |
| Message Type | Queue | |
| Route | Type | |
| Service | Synonym | |
| Remote Service Binding | Aggregate | |
| Fulltext Catalog | XML schema collection | |
| Certificate | ||
| Asymmetric Key | ||
| Symmetric Key | ||
| Contact | ||
| Schema Assemblies | ||
| Schemas |
Permissions
The permissions link principals with securables. For example, a SQL LOGIN (the principal) needs to CREATE (the permission) databases (the securable). Together, these three elements represent a complete security assignment.
Managing Permissions
- A GRANT of a permission removes any REVOKE or DENY on a securable. For example, if a table has SELECT permission denied on it and then the SELECT permission is granted, the DENY permission will then be removed on that table.
- DENY and REVOKE remove any GRANT permission on a securable. REVOKE removes any GRANT or DENY permission on a securable.
- Permissions denied at a higher scope in the security model override grants on that permission at a lower scope. Keep in mind that the security model has the server scope at the highest level, followed by database and schema. So, if INSERT permission is denied on tables at the database level, and INSERT on a specific table in that database is granted at the schema level, the result is that INSERT is denied on all tables. In this example, a database-level DENY overrides any GRANT at the lower schema level.
- Permissions granted at a higher scope in the security model are overridden by a DENY permission at a lower level. For example, if INSERT permission is granted on all tables at the database scope, and INSERT is denied on a specific table in the database (schema scope), INSERT is then denied on that specific table.
Statement permissions have nothing to do with the actual data; they allow users to create the structure that holds the data.
- Create Database
- Create Table
- Create View
- Create Procedure
- Create Index
- Create Rule
- Create Default
The second type of permissions is object permissions, which control how users work with the actual data.
| Object Permission | Description |
| Control | This permission gives the principal ownership-like capabilities on the object and all objects under it in the hierarchy. For example, if you grant a user Control permission on the database, then the user has Control permission on all the objects in the database, such as tables and views. |
| Alter | This permission allows users to create, alter, or drop the securable and any object under it in the hierarchy. The only property the user can’t change is ownership. |
| Take Ownership | This allows the user to take ownership of an object. |
| Impersonate | This permission allows one login or user to impersonate another. |
| Create | As the name implies, this permission lets a user create objects. |
| View Definition | This permission allows users to see the Transact-SQL syntax that was used to create the object being secured. |
| Select | When granted, this permission allows users to read data from the table or view. When granted at the column level, it lets users read from a single column. |
| Insert | This permission allows users to insert new rows into a table. |
| Update | This permission lets users modify existing data in a table but not add new rows to or delete existing rows from a table. When this permission is granted on a column, users can modify data in that single column. |
| Delete | This permission allows users to remove rows from a table. |
| References | Tables can be linked together on a common column with a foreign-key relationship, which is designed to protect data across tables. When two tables are linked with a foreign key, this permission allows the user to select data from the primary table without having Select permission on the foreign table. |
| Execute | This permission allows users to execute the stored procedure where the permission is applied. |
| All the permissions in SQL Server can exist in one of three states: | |
| Grant | Granting allows users to use a specific permission. For instance, if you grant SmithB Select permission on a table, then SmithB can read the data within. You know a permission has been granted when the Allow check box is selected next to the permission in the permissions list |
| Revoke | A revoked permission isn’t specifically granted, but a user can inherit the permission if it has been granted to another role of which they are a member. That is, if you revoke the Select permission from SmithB, then SmithB can’t use it. If, however, SmithB is a member of a role that has been granted Select permission, SmithB can read the data just as if SmithB had the Select permission. A permission is revoked when neither Allow nor Deny boxes are selected next to a permission. |
| Deny | If you deny a permission, the user doesn’t get the permission—no matter what. If you deny SmithB Select permission on a table, even if SmithB is a member of a role with Select permission, then SmithB can’t read the data. You know a permission has been denied when the Deny check box is selected next to the permission in the permissions list. |
Tags: Permissions, Securables, Security