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.
|
1 2 3 4 5 6 7 8 9 |
Use Master Go CREATE LOGIN Demologin WITH PASSWORD = 'P@ssw0rd!23' GO USE [AdventureWorks2017]; GO CREATE USER Demologin FROM LOGIN Demologin; Go GRANT CONTROL ON SCHEMA::Person TO Demologin; |
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.
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.
|
1 2 3 4 |
DENY CONTROL ON [Person].[Person] to DemoLogin Go EXECUTE AS USER = 'Demologin'; SELECT * FROM fn_my_permissions ('Person.Person', 'OBJECT'); |
Look at the following T-SQL query that grants select first and then deny it on the [Person].[Person] table.
|
1 2 3 4 5 6 |
Grant Select ON [Person].[Person] to DemoLogin DENY Select ON [Person].[Person] to DemoLogin EXECUTE AS USER = 'Demologin'; select Top 2 * from Person.person REVERT; GO |
The user cannot view table records and gets an error that the select permission is denied on the object.
However, if we reverse the Grant and DENY statement order for Azure SQL Database, it works fine.
|
1 2 3 4 5 6 |
DENY select ON [Person].[Person] to DemoLogin Grant select ON [Person].[Person] to DemoLogin EXECUTE AS USER = 'Demologin'; select top 2 *from Person.person REVERT; GO |
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.
|
1 2 3 4 5 6 7 8 |
DENY CONTROL ON SCHEMA::Person TO Demologin; Go GRANT select ON [Person].[Person] to DemoLogin Go EXECUTE AS USER = 'Demologin'; select top 2 *from Person.person REVERT; GO |
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.
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.
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.
|
1 2 3 4 5 6 7 8 |
GRANT SELECT ON SCHEMA :: Sales TO Demologin; GO REVOKE SELECT ON SCHEMA :: Sales TO Demologin; GO EXECUTE AS USER = 'Demologin'; select top 2 *from Person.person REVERT; GO |
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.
We can use the REVOKE statement to remove the permission given by grant or deny statements.
|
1 2 3 4 5 6 7 8 9 10 |
Grant CONTROL ON SCHEMA::[HumanResources] TO Demologin; Go DENY SELECT ON OBJECT:: [HumanResources].[Employee] TO Demologin; GO REVOKE SELECT ON OBJECT:: [HumanResources].[Employee] TO Demologin; GO EXECUTE AS USER = 'Demologin'; select top 2 *from [HumanResources].[Employee] REVERT; GO |
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.
|
1 2 3 4 5 6 7 8 9 10 |
Grant CONTROL ON SCHEMA::[HumanResources] TO Demologin; Go GRANT SELECT ON OBJECT:: [HumanResources].[Employee] TO Demologin; GO REVOKE SELECT ON OBJECT:: [HumanResources].[Employee] TO Demologin; GO EXECUTE AS USER = 'Demologin'; select top 2 *from [HumanResources].[Employee] REVERT; GO |
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.
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.
|
1 |
Grant execute on [HumanResources].[uspUpdateEmployeeHireInfo] to [DemoLogin] WITH GRANT OPTION; |
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.
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].
|
1 2 |
EXECUTE AS USER = 'Demologin'; Grant execute on [HumanResources].[uspUpdateEmployeeHireInfo] to [Testuser] ; |
The query executes successfully. You can validate the permissions using the fn_my_permissions() function.
|
1 2 3 4 |
EXECUTE AS USER = 'Testuser'; SELECT * FROM fn_my_permissions ('HumanResources.uspUpdateEmployeeHireInfo', 'OBJECT'); REVERT; GO |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023