2

In SQL Server 2016 SP1 standard edition we can use dynamic-data-masking. The masking of data is controlled using the built-in security, for example:

REVOKE UNMASK TO user_who_cannot_see_senstive_data

Of course, this is not working for the users who are members of the db_owner database role.

Is there a list with security roles or cases showing when the REVOKE UNMASK is going to work?

I have found a database engine permissions but it's too complicated and does not seem to show when data cannot be masked from a user.

1 Answer 1

1

How to check which database roles can see masked columns?

Normal query which checks for users permissions seems to do the trick

    select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id

I tested it on below sample script from msdn

CREATE TABLE Membership  
  (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 Membership (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]');  

CREATE USER TestUser WITHOUT LOGIN;  
GRANT SELECT ON Membership TO TestUser;  


GRANT UNMASK TO TestUser;  
EXECUTE AS USER = 'TestUser';  
SELECT * FROM Membership;  
REVERT;  

now querying gives me below result

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.