Background
In SQL Server/Azure SQL, database-level permissions are almost always handled by either the database owner (which has the dbo-identity) or members of the db_owner or db_securityadmin-database roles.
Here you can learn more about the differences: Principals (Database Engine), Database-Level Roles.
Both roles can GRANT/DENY/REVOKE any permission within a database. Since that makes members of these roles super powerful, their membership should always be tightly controlled and audited. (also see: Security concept: Audit Trail).
Consequently, most customers just keep this power to the main Administrators-group.
However, there are cases where it is useful to enable a set of non-admin users—let’s call them LowPermission-Managers—to work in a more self-sufficient way and enable them to grant some permissions to other users without the need to involve “the Administrators”.
The emphasis is on “some permissions” asthis only makes sense for low privileges. Making the LowPermissionManagers members of db_owner or db_securityadmin -roles would defeat the purpose.
For example, it can be useful to allow the LowPermissionManagers to grant the SELECT, INSERT, UPDATE and DELETE permissions to other users. But not allow other permissions like ALTER or CONTROL and so on.
To put things into perspective,here is an overview of the extensive permissions that the SQL Server engine ships with: Permissions (Database Engine)
You will also see me using the term “securable”. In a nutshell, a securable is the object on which a user (aka the “grantee”) will get certain permissions. (Securables - SQL Server | Microsoft Learn)
In other words, we want to allow someone else to take over some of the work of granting privileges. This concept is also called “delegation”. (Here you can read more on Delegation of Authority.)
SQL Server/Azure SQL has a rich permission system, and I want to show you two methods to accomplish delegation of permission-management.
Using roles for delegating permission-management on a schema
In general, when granting permissions to users in a database, we should always use roles instead of granting permissions to individual users. This is the same best practice used for Windows Active Directory and Entra ID groups (formerly known as Azure Active Directory: “Azure AD is becoming Microsoft Entra ID “).
Note
In Windows Active Directory the acronyms AGDLP (Account -> Global group -> Domain Local group -> Permission) and AGLP are well known amongst administrators.
In SQL Server the acronym URP (User -> Role -> Permission) or LURP (Login -> User -> Role -> Permission) is less commonly used but is based on the similar concept: to grant permissions not to individual accounts but to a collection, strored as group or role.
In my example we have a database which contains several user-defined schemas (Ownership and user-schema separation in SQL Server - SQL Server | Microsoft Learn). The schema called “SensorData” contains numerous tables.
The database administrator (let’s call that person DBA) does not want to be bothered with managing simple read-access to this specific schema and all the tables and views it contains.
Instead, this will be delegated to a different user, Alejandra.
To set this up, the DBA (1) creates a database role called “role_SensorDataUsers” and (2) gives that role the SELECT-permission for the whole SensorData-schema.
CREATE ROLE role_SensorDataUsers
GO
GRANT SELECT
ON SCHEMA::SensorData
TO role_SensorDataUsers
GO
Now instead of adding every other user that needs access to this schema, the DBA (3) delegates the permission to add members for this specific database role – and no other role! – to Alejandra, who he trusts with this task.
In SQL Server we have the ALTER ROLE permission, which can be granted on a per-role basis. Granting the ALTER ROLE permission allows the grantee, Alejandra, to add and remove (drop) members to and from this role.
GRANT ALTER
ON ROLE::role_SensorDataUsers
TO Alejandra
Now, Alejandra can run the following statement to (4) add Guillermo, a new user, to the role_SensorDataUsers-role:
ALTER ROLE role_SensorDataUsers
ADD MEMBER Guillermo
Result: Guillermo can now SELECT from any table or view contained in the schema SensorData.
When it comes to Alejandra: all she can do is add or drop other users to and from this role. She cannot change the permissions of the role or drop it.
Note
The ALTER ROLE-permission also includes the ability to change the name of the role. This is something to be aware of. Renaming may not sound dangerous, but confusion can lead to mistakes. Therefore, make sure the ALTER ROLE-statement is being audited (as it should be anyways).
Finally, when the time comes, Alejandra can remove Guillermo from this role:
ALTER ROLE role_SensorDataUsers
DROP MEMBER Guillermo
With that, the database administrator does not need to bother with granting specific permissions on specific schemas and can focus on higher privileges and user-creation.
This is a diagram that depicts the setup:
Using the WITH GRANT OPTION of the GRANT statement to delegate management of multiple permissions on schema-level
Imagine we not only want to delegate the management of just the SELECT-permission, but also INSERT, UPDATE and DELETE on a given schema to a different user: Alejandra. And we want Alejandra to decide which of these 4 permissions to grant herself.
We could create 4 roles of course, each with one of the 4 permissions only, and make Alejandra manager of the 4 roles, following the previous example.
But there is another option which I would like to demonstrate:
Instead of having 4 roles, in this case we could make use of the WITH GRANT OPTION of the GRANT statement, documented here: GRANT (Transact-SQL) - SQL Server | Microsoft Learn
When using the GRANT statement together with the WITH GRANT OPTION, we are allowing the grantee to pass the very same permissions on to other users.
This is how it looks using the previous example as a baseline.
We could grant Alejandra the respective permissions directly but, following best practices, the DBA (1) creates a role for this purposein a very different way than in the previous example.
CREATE ROLE role_SensorDataUserManager
GO
The DBA (2) grants this role the 4 permissions which he wants to delegate the management for:
GRANT SELECT, INSERT, UPDATE, DELETE
ON SCHEMA::SensorData
TO role_SensorDataUserManager WITH GRANT OPTION
Now the DBA (3) makes Alejandra a member of this role:
ALTER ROLE role_SensorDataUserManager
ADD MEMBER Alejandra
At this point I want to point out how SQL Server stores permissions in metadata. To do that we can run the following T-SQL:
SELECT * FROM sys.database_principals
WHERE NAME IN ('Alejandra','Guillermo','role_SensorDataUserManager')
SELECT * FROM sys.database_permissions
where grantee_principal_id IN (5, 6, 7) -- IDs of the 3 principals used in this demo
This is the result:
Note that (A) Alejandra (principal_id = 5) has no permissions on her own. Instead, the role role_SensorDataUserManager (principal_id = 7, marked red in my image) has the permissions. And (B) The permissions that it has are not simply “GRANT” but really “GRANT WITH GRANT OPTION” (marked red in my image).
This is how this looks like in SQL Server Management Studio UI:
As a result, not only can Alejandra do what the role allows: SELECT, INSERT, UPDATE, DELETE on any table or view within the schema SensorData, but in addition to that, she can decide to give any of these 4 permissions to someone else – without making that other user member of the role role_SensorDataUserManager:
GRANT INSERT, UPDATE ON SCHEMA::SensorData
TO Guillermo
AS role_SensorDataUserManager
If you look at this statement, you see that it contains the AS-Clause of the GRANT-statement.
This is important, because without this clause Alejandra cannot grant the permissions to Guillermo. When utilizing the power of the WITH GRANT OPTION which is granted to a role, and not the user-account directly, it is required to explicitly state the principal which is used for the GRANT.
Because behind the scenes, SQL Server always stores the grantor’s principal_id in metadata. And since the permission could come from different roles it needs to be specified (a good example of explicitness, which is fundamental to security controls, in my eyes).
Let’s look at sys.database_permissions again:
SELECT * FROM sys.database_permissions
where grantee_principal_id = 12 -- role_SensorDataUserManager principal_id on my system
These are the 2 permissions that have been granted to Guillermo (grantee_principal_id = 6, marked green in my image) in the name of the role role_SensorDataUserManager (principal_id = 7, marked red in my image). This time the role is the grantor, not the grantee.
Result: Guillermo can now run the INSERT and UPDATA-statements against any table or view contained in the schema SensorData.
This is a diagram that shows this approach:
Of course, Alejandra can also REVOKE the permissions.
Here again it is required to call out the principal name from which the permissions originate. Otherwise, the SQL engine cannot find the right permission-entry to remove.
REVOKE INSERT, UPDATE ON SCHEMA::SensorData
TO Guillermo
AS role_SensorDataUserManager
Important
When using the GRANT… WITH GRANT OPTION statement it is important to have a good understanding on how the permissions are stored. You may know that when database users own objects such as tables within the database, those objects cannot be dropped as long as they are owned by the user which you want to drop In such cases you either drop the objects completely, or change their ownership to a different user account.
The same applies to permission grantors. In my scenario the permission was saved using the role’s principal-id. Hence the role cannot be dropped before the permissions that were granted under its authority are revoked (not denied either).
The error message in such an attempt would be:
Msg 15284, Level 16, State 1, Line 96
The database principal has granted or denied permissions to objects in the database and cannot be dropped.
Summary
I have shown 2 methods for delegation of permission-management.
Generally, I always strongly recommend exclusively working with roles that have permissions and absolutely avoid granting permissions on a user-level. This makes reporting and auditing much easier and avoids problems down the road.
However, at times, it can be useful to explicitly save who granted which permissions in metadata, or the use of roles may not always be practical. The GRANT WITH GRANT OPTION is a niche player for sure, but it's good to have alternatives when needed.
Happy securing
Andreas
Thank you to the following people for help with this article:
Practical use cases: Ralf Dietrich, Managing director Sarpedon Quality Lab
Technical correctness: Steven Gott (Principal Software Engineer, Microsoft)
Language: Tameika Lawrence (Program Manager SQL Security)