Confusion about granting UNMASK permissions to a SQL user.

Question

I am trying to understand who can grant UNMASK permissions for the database at the top most level.

For example, I have implemented SQL Dynamic Data masking and from what I am reading, granting the UNMASK permission on a user can be done by:

1. Users with the UNMASK permission plus the WITH GRANT option.
2. Users with the CONTROL SERVER server permission (is it implied implied to be able to grant, or only UNMASK?).
3. Users with the CONTROL database permission (is it implied implied to be able to grant, or only UNMASK?).

But who can grant the CONTROL SERVER or CONTROL permission on a user? Is it only the object owner? Or can other users with the CONTROL SERVER or CONTROL permissions plus the WITH GRANT option do the same?

Answer 1

Hello @Guerrero, Erik,

Welcome to the MS Q&A platform.

To grant unmask permissions, your point number 1 and 3 are correct. Point number 2 won't be applicable to Azure SQL.

To demonstrate this.

Case1:

A user with Unmask permissions plus with grant option, can provide unmask permissions to a new user

On master DB

CREATE LOGIN MaskingTestUser WITH PASSWORD=N'Password@12345'

CREATE USER MaskingTestUser FROM LOGIN MaskingTestUser

On user DB

CREATE USER MaskingTestUser FROM LOGIN MaskingTestUser

GRANT UNMASK TO MaskingTestUser WITH GRANT OPTION;

GO

on master

CREATE LOGIN MaskingTestUser1 WITH PASSWORD=N'Password@12345'

CREATE USER MaskingTestUser1 FROM LOGIN MaskingTestUser

On user DB

CREATE USER MaskingTestUser1 FROM LOGIN MaskingTestUser1

login to SSMS using MaskingTestUser

GRANT UNMASK TO MaskingTestUser1

Commands completed successfully.

Able to grant unmask permissions.

Case2: Users with the CONTROL SERVER server permission (is it implied implied to be able to grant, or only UNMASK?).

In Azure SQL, granting server level permissions is not supported.

USE master;

GRANT CONTROL SERVER TO MaskingTestUser;

GO

Msg 40521, Level 16, State 1, Line 12

Securable class 'server' not supported in the server scope in this version of SQL Server.

Case3:

Users with the CONTROL database permission (is it implied implied to be able to grant, or only UNMASK?).

You will need to grant control permissions at DB level to provide unmask permissions.

GRANT CONTROL ON DATABASE::test TO MaskingTestUser

from SSMS open new query with MaskingTestUser

GRANT UNMASK TO MaskingTestUser1

A principal that has been granted CONTROL can also grant permissions on the securable. Because the SQL Server security model is hierarchical, CONTROL at a particular scope implicitly includes CONTROL on all the securables under that scope.

For example, CONTROL on a database implies all permissions on the database, all permissions on all assemblies in the database, all permissions on all schemas in the database, and all permissions on objects within all schemas within the database.

To answer your question members of db_owner , db_securityadmin and db_accessadmin can grant control permissions to an user in Azure SQL DB

Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server

Members of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions

Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

Please see the below document for your reference:

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16

I hope this helps. Please let me know if you have any further questions.