Only authorized users are able to execute statements or perform operations on an entity. If an unauthorized user attempts to do either task, the execution of the Transact-SQL statement or the operation on the database object will be rejected.
There are three Transact-SQL statements related to authorization:
GRANT
DENY
REVOKE
Before you read about these three statements, I will repeat the most important facts concerning the security model of the Database Engine. The model separates the world into principals and securables. Every securable has associated permissions that can be granted to a principal. Principals, such as individuals, groups, or applications, can access securables. Securables are the resources to which the authorization subsystem regulates access. There are three securable classes: server, database, and schema, which contain other securables, such as logins, database users, tables, and stored procedures.
1. GRANT Statement
The GRANT statement grants permissions to securables. The syntax of the GRANT statement is
GRANT {ALL [PRIVILEGES]} | permission_list
[ON [class::] securable] TO principal_list [WITH GRANT OPTION]
[AS principal ]
The ALL clause is a deprecated feature and is maintained only for backward compatibility.
It does not grant all possible permissions, as the name implies. (For the list of specific permissions, see Microsoft Docs.) permission_list specifies either statements or objects (separated by commas) for which the permissions are granted. class specifies either a securable class or a securable name for which permission is granted. ON securable specifies the securable for which permissions are granted (see Example 12.20 later in this section). principal_list lists all accounts (separated by commas) to which permissions are granted. principal and the components of principal_list can be a user account, a login or user account mapped to a certificate, a login mapped to an asymmetric key, a database user, a database role, or an application role.
Table 12-3 lists and describes all the permissions and lists the corresponding securables to which they apply.
NOTE Table 12-3 shows only the most important permissions. The security model of the Database Engine is hierarchical. Hence, there are many granular permissions that are not listed in the table. You can find the description of these permissions in Microsoft Docs.
The following examples demonstrate the use of the GRANT statement. To begin, Example 12.16 demonstrates the use of the CREATE permission.
Example 12.16
USE sample;
GRANT CREATE TABLE, CREATE PROCEDURE
TO peter, mary;
In Example 12.16, the users peter and mary can execute the Transact-SQL statements CREATE TABLE and CREATE PROCEDURE. (As you can see from this example, the GRANT statement with the CREATE permission does not include the ON option.)
Example 12.17 allows the user mary to create user-defined functions in the sample database.
Example 12.17
USE sample;
GRANT CREATE FUNCTION TO mary;
Example 12.18 shows the use of the SELECT permission within the GRANT statement.
Example 12.18
USE sample;
GRANT SELECT ON employee
TO peter, mary;
In Example 12.18, the users peter and mary can read rows from the employee table.
NOTE When a permission is granted to a user account or a login, this account (login) is the only one affected by the permission. On the other hand, if a permission is granted to a group or role, the permission affects all users belonging to the group (role).
Example 12.19 shows the use of the UPDATE permission within the GRANT statement.
Example 12.19
USE sample;
GRANT UPDATE ON works_on (emp_no, enter_date) TO peter;
After the GRANT statement in Example 12.19 is executed, the user peter can modify values of two columns of the works_on table: emp_no and enter_date.
Example 12.20 shows the use of the VIEW DEFINITION permission, which controls the ability of users to read metadata.
Example 12.20
USE sample;
GRANT VIEW DEFINITION ON OBJECT::employee TO peter;
GRANT VIEW DEFINITION ON SCHEMA::dbo TO peter;
Example 12.20 shows two GRANT statements for the VIEW DEFINITION permission.
The first one allows the user peter to see metadata about the employee table of the sample database. (OBJECT is one of the base securables, and you can use this clause to give permissions for specific objects, such as tables, views, and stored procedures.) Because of the hierarchical structure of securables, you can use a “higher” securable to extend the VIEW DEFINITION (or any other base) permission. The second statement in Example 12.20 gives the user peter access to metadata of all the objects of the dbo schema of the sample database.
NOTE The VIEW DEFINITION permission allows you to grant or deny access to different pieces of your metadata and hence to decide which part of metadata is visible to other users.
Example 12.21 shows the use of the CONTROL permission.
Example 12.21
USE sample;
GRANT CONTROL ON DATABASE::sample TO peter;
In Example 12.21, the user peter effectively has all defined permissions on the securable (in this case, the sample database). A principal that has been granted CONTROL also implicitly has the ability to grant permissions on the securable; in other words, the CONTROL permission includes the WITH GRANT OPTION clause (see Example 12.22). The CONTROL permission is the highest permission in relation to several base securables. For this reason, CONTROL at a particular scope implicitly includes CONTROL on all the securables under that scope. Therefore, the CONTROL permission of user peter on the sample database implies all permissions on this database, all permissions on all assemblies in the database, all permissions on all schemas in the sample database, and all permissions on objects within the sample database.
By default, if user A grants a permission to user B, then user B can use the permission only to execute the Transact-SQL statement listed in the GRANT statement. The WITH GRANT OPTION gives user B the additional capability of granting the privilege to other users, as shown in Example 12.22.
Example 12.22
USE sample;
GRANT SELECT ON works_on TO mary
WIDTH GRANT OPTION;
In Example 12.22, the user mary can use the SELECT statement to retrieve rows from the works_on table and also may grant this privilege to other users of the sample database.
2. DENY Statement
The DENY statement prevents users from performing actions. This means that the statement removes existing permissions from user accounts or prevents users from gaining permissions through their group/role membership that might be granted in the future. This statement has the following syntax:
DENY {ALL [PRIVILEGES] } | permission_list
[ON [class::] securable] TO principal_list
[CASCADE] [ AS principal ]
All options of the DENY statement have the same logical meaning as the options with the same name in the GRANT statement. DENY has an additional option, CASCADE, which specifies that permissions will be denied to user A and any other users to whom user A passed this permission.
(If the CASCADE option is not specified in the DENY statement, and the corresponding object permission was granted with the WITH GRANT OPTION, an error is returned.)
The DENY statement prevents the user, group, or role from gaining access to the permission granted through their group or role membership. This means that if a user belongs to a group (or role) and the granted permission for the group is denied to the user, this user will be the only one of the group who cannot use this permission. On the other hand, if a permission is denied for a whole group, all members of the group will be denied the permission.
NOTE You can think of the GRANT statement as a “positive” user authorization and the DENY statement as a “negative” user authorization. Usually, the DENY statement is used to deny already existing permissions for groups (roles) to a few members of the group.
Examples 12.23 and 12.24 show the use of the DENY statement.
Example 12.23
USE sample;
DENY CREATE TABLE, CREATE BROCHURE
TO peter;
The DENY statement in Example 12.23 denies two previously granted statement permissions to the user peter.
Example 12.24
USE sample;
GRANT SELECT ON project
TO PUBLIC;
DENY SELECT ON project
TO peter, mary;
Example 12.24 shows the negative authorization of some users of the sample database. First, the retrieval of all rows of the project table is granted to all users of the sample database. After that, this permission is denied to two users: peter and mary.
NOTE Permissions denied at a higher scope of the Database Engine security model override granted permissions at a lower scope. For instance, if SELECT permission is denied on the level of the sample database, and SELECT is granted on the employee table, the result is that SELECT is denied to the employee table as well as all other tables.
3. REVOKE Statement
The REVOKE statement removes one or more previously granted or denied permissions. This statement has the following syntax:
REVOKE [GRANT OPTION FOR]
{ [ALL [PRIVILEGES] ] | permission_list ]}
[ON [class:: ] securable ]
FROM principal_list [CASCADE] [ AS principal ]
The only new option in the REVOKE statement is GRANT OPTION FOR. (All other options have the same logical meaning as the options with the same names in the GRANT or DENY statement.) GRANT OPTION FOR is used to remove the effects of the WITH GRANT OPTION in the corresponding GRANT statement. This means that the user will still have the previously granted permissions but will no longer be able to grant the permission to other users.
NOTE The REVOKE statement revokes “positive” permissions specified with the GRANT statement as well as “negative” permissions generated by the DENY statement. Therefore, its function is to neutralize the specified (positive or negative) permissions.
Example 12.25 shows the use of the REVOKE statement.
Example 12.25
USE sample;
REVOKE SELECT ON project FROM PUBLIC;
The REVOKE statement in Example 12.25 revokes the granted permission for the public role. At the same time, the existing “negative” permissions for the users peter and mary are not revoked (as in Example 12.24), because the explicitly granted or denied permissions are not affected by revoking roles or groups.
4. Managing Permissions Using SQL Server Management Studio
Database users can perform activities that are granted to them. In this case, there is a corresponding entry in the sys.database_permissions catalog view (that is, the value of the state column is set to G for grant). A negative entry in the table prevents a user from performing activities. The entry D (deny) in the state column overrides a permission that was granted to a user explicitly or implicitly using a role to which the user belongs. Therefore, the user cannot perform this activity in any case. In the last case (value R), the user has no explicit privileges but can perform an activity if a role to which the user belongs has the appropriate permission.
To manage permissions for a user or role using Management Studio, expand the server and expand Databases. Right-click the database and click Properties. Choose the Permissions page and click the Search button. In the Database Properties dialog box, shown in Figure 12-6 for the user mary, you can select one or more object types (users and/or roles) to which you want to grant or deny permissions. To grant a permission, check the corresponding box in the Grant column and click OK. To deny a permission, check the corresponding box in the Deny column. (The With Grant column specifies that the user has the additional capability of granting the privilege to other users.) Blanks in these columns mean no permission.
To manage permissions for a single database object using Management Studio, expand the server, expand Databases, expand the database, and then expand Tables, Views, or Synonyms, depending on the database object for which you want to manage permissions. Right-click the object, choose Properties, and select the Permissions page. (Figure 12-7 shows the Table Properties dialog box for the department table.) Click the Search button to open the Select Users or Roles dialog box. Click Object Types and select one or more object types (users, database roles, application roles). After that, click Browse and check all objects to which permissions should be granted. To grant a permission, check the corresponding box in the Grant column. To deny a permission, check the corresponding box in the Deny column.
5. Managing Authorization and Authentication of Contained Databases
As you already know from Chapter 5, contained databases have no configuration dependencies on the server instance where they are created and can therefore be easily moved from one instance of the Database Engine to another one. In this section you will learn how to authenticate users for contained databases. Each user that belongs to a contained database is not tied to a login, because such a user has no external dependencies and can be attached elsewhere.
Example 12.26 shows the creation of such a user.
Example 12.26
USE my_sample;
CREATE USER my_login WITH PASSWORD = ‘x1y2z3w4?’;
Example 12.26 creates a user my_login that is not tied to a login. (The my_sample database is a contained database that was created in Example 5.20.) If you try to create such a user in a conventional database, you get the following error:
Msg 33233, Level 16, State 1, Line 1
You can only create a user with a password in a contained database.
The sp_migrate_user_to_contained procedure converts a database user that is mapped to a login to a contained database user with a password. sp_migrate_user_to_contained separates the user from the original login, so that settings such as password and default language can be administered separately for the contained database. This system stored procedure removes dependencies on the instance of the Database Engine and can be used before moving the contained database to a different server instance.
Also, you can use the dynamic management view called sys.dm_db_uncontained_entities to learn which parts of your database cannot be moved to a different server instance.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.