Rajendra Gupta
Check WITH GRANT permission in SSMS

Grant, With Grant, Revoke and Deny statements in SQL Server and Azure SQL Database

September 6, 2021 by

This article will walk you through Grant, With Grant, Revoke and Deny statements for both on-premises and Azure SQL Database.

Principals, Securable and Permissions

It is an essential and critical task for a DBA to manage SQL Server database permissions at instance, database, or object level. You must protect sensitive, personal data from unauthorized access. Similarly, you require to assign relevant permissions to a user or role. Sometimes, database professionals have confusion over database-level security in Azure SQL Database. DBA’s responsibility is to make sure only relevant users have access to database objects and authorization for performing tasks such as insert, update, delete, alter, select.

For example, certain users might need only Select permissions on specific objects. Similarly, another user group requires permissions for database backups, creating databases, performing DML (Insert, Update, Delete) operations.

Before we explore the Grant, With Grant, Revoke and Deny statements in SQL Server, let’s understand the essential security components for on-premises and Azure SQL Database.

  • Principals: The principals are individual users, groups, or processes requiring access to SQL Server instances, databases, or objects. It includes server-level principals as well as database-level principals
  • Securable: The securable is the server and database level components for assigning permissions
    • Server-level securable: It includes databases, Endpoint, login, server role, availability group
    • Database-level securable: It includes application role, certificate, full-text catalogs, database role, schema, the user
    • Schema-level securable: It includes tables, views, stored procedures, functions
  • Permissions: The permission defines the type of access. For example, you may assign permissions to view records or access to perform Insert, Update, Delete operations for a table. If you assign permission to a higher level, it is reflected in the lower-level components. For example, if you have given instance-level[sysadmin] rights, the user gets all permissions at the database level too
  • Note: You can download and refer to Microsoft SQL Server and Azure SQL Database permissions poster using the link

Let’s go forward and explore Grant, With Grant, Revoke and Deny statements in SQL Server and Azure SQL Database.

Grant, With Grant, Revoke and Deny statements

Let’s view the definitions of Grant, With Grant, Revoke and Deny statements.

  • Grant: The Grant statement gives permission on a specified securable to the principal. The syntax for Grant for SQL Server and Azure SQL Server is as below:

    Grant <Permission> on <Securable> to <user, login, Group

  • Revoke: The Revoke statement removes the previously granted or denied permissions
  • Deny: The deny statement denies permission to a principal for accessing the securable. For example, if we do not want a user to perform a delete operation on a table, DBA can deny delete permission on the object

Demonstration of Grant, Deny and Revoke in Azure SQL Database

Let’s explore Grant, Deny and Revoke practically and understand their behavior, differences. The below script creates a SQL Login, user, and role for providing CONTROL permissions on [Person] schema.

To view the effective permission, we can use the function fn_my_permissions(). As shown below, the control permission gives Select, Insert, Update, delete, execute, alter permissions on [Person] schema.

Azure SQL Database security function

If we check the user permission on a different schema that says [sales], you do not get any row in return because it does not have any permissions on that schema.

Now, suppose you do not want any permissions on [Person].[Person] table for the [Demologin] user. In this case, we can deny permissions using the below query.

DENY permission statement

Look at the following T-SQL query that grants select first and then deny it on the [Person].[Person] table.

The user cannot view table records and gets an error that the select permission is denied on the object.

Grant select permission with Deny statements

However, if we reverse the Grant and DENY statement order for Azure SQL Database, it works fine.

Check relevant access

Suppose you denied CONTROL permission on the [Person] schema. Later, you grant permission for select records on the [Person].[Person] table. Will the user be able to access the table? Let’s execute the below query and view the results.

It cannot retrieve the records because the CONTROL permission is denied at the higher scope (schema level). In this case, the deny takes precedence, and the user cannot access the table.

Denied Select permission error

Similarly, if you have granted higher scope permission in Azure SQL Database but deny at a lower level, deny always takes precedence. As shown below, the control access is granted at [Person] schema; however, select permission was denied for [Person].[Person] table. If you try accessing the records for the table, you get the error as shown below.

Higher permission scope

Revoke permissions

The revoke statement removes previously granted or denied permissions of the Azure SQL Database. For example, the following SQL query grants select permission on sales schema to [Demologin]. Later, we revoked the permission. In this case, Revoke removes the previously granted permission.

Revoke permissions

Let’s consider another scenario where you have granted control access on the [HumanResources] schema to [DemoLogin] user. Later, you denied select permissions for a specific object [HumanResources].[Employee]. As highlighted earlier, deny takes precedence; therefore, the user cannot view records of the table.

Check DB access after revoke

We can use the REVOKE statement to remove the permission given by grant or deny statements.

Grant or Deny statements

In the following SQL statement, we revoke the select permission for the object [HumanResources].[Employee]. Still, the user can view the table records because Revoke removed the previously granted select permissions. However, the user has control permission at a higher precedence schema level. Therefore, due to control permission at the schema level, the user can run the select statement in SQL Server.

With Grant

If you open database user properties in SQL Server Management Studio and go to securable, you get an additional option – With Grant, as highlighted below.

Check WITH GRANT permission in SSMS

The With Grant option resembles that security principal who receives permission can grant the same permission to other security principals. You need to use WITH GRANT OPTION keyword for this purpose.

The following query assigns execute permissions on the specified stored procedure ([HumanResources].[uspUpdateEmployeeHireInfo]) using the WITH GRANT OPTION keyword.

Open the [DemoLogin] user properties in SSMS and the securable for the [HumanResources].[uspUpdateEmployeeHireInfo], it has a tick mark on both Grant and With Grant checkboxes.

Check database user properties in SSMS

Now, the user [DemoLogin] can assign the same permission to another database user. For example, it assigns execute permission on the specified SP to the user [Testuser].

The query executes successfully. You can validate the permissions using the fn_my_permissions() function.

Check permissions granted using WITH GRANT Statement

Conclusion

This article explored differences between Grant, With Grant, Deny and Revoke statements for controlling and managing access for server and database level objects. You should manage database security efficiently and avoid assigning a higher level of permissions to unintended people. You can deny access to certain users or groups for controlling access proactively.

Rajendra Gupta
Azure, Security, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

863 Views