SQL Server Data Masking Minimum permissions needed to mask

Question

I am in the process of masking some data in a SQL Server 2017 database. I have masked a column with this statement:

ALTER TABLE lEmployee
    ALTER COLUMN FirstName nvarchar(160) 
          MASKED WITH (FUNCTION = 'partial(2,"xxx",2)')

And I can see in sys.columns that this is now masked.

Now in the application, the data is not showing as masked (neither is it in the db when logged in as that user). I have also run the below to ensure the user does not have this permission.

REVOKE UNMASK TO testuser

Can anyone advise what the minimum permissions are needed so that the data masking works as expected.

Thank you in advance

Answer 1

You are not able to DENY UNMASK on sysadmin and db_owner. This is build-in limitation.

Also, as noted in the documentation, this is not a security feature if the user can query the table.

As it in the example of the documentation shows, you do not need to grant REVOKE UNMASK as it is set by default:

DROP TABLE IF EXISTS  [dbo].[StackOverflow];

CREATE TABLE [dbo].[StackOverflow]  
  (MemberID int IDENTITY PRIMARY KEY,  
   FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,  
   LastName varchar(100) NOT NULL,  
   Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,  
   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);  

INSERT [dbo].[StackOverflow] (FirstName, LastName, Phone#, Email) VALUES   
('Roberto', 'Tamburello', '555.123.4567', '[email protected]'),  
('Janice', 'Galvin', '555.123.4568', '[email protected]'),  
('Zheng', 'Mu', '555.123.4569', '[email protected]');  
SELECT * FROM [dbo].[StackOverflow]  ;  



CREATE USER TestUser WITHOUT LOGIN;  
GRANT SELECT ON [dbo].[StackOverflow]   TO TestUser;  

EXECUTE AS USER = 'TestUser';  
SELECT * FROM [dbo].[StackOverflow]  ;  
REVERT; 

and you cannot REVOKE UNMASK to sysadmin and db_owner.