The default behavior is masking data . Users will not lose their access (if you intend the capability of doing SELECT data from a table) but they will see masked data.
Users will see data if you grant UNMASK permission explicitly.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hello all,
If I add a masking rule on an existing table using ALTER, would all the users that already had access to the
sensitive data lose their access and would require the UNMASK permission?
thank you.
The default behavior is masking data . Users will not lose their access (if you intend the capability of doing SELECT data from a table) but they will see masked data.
Users will see data if you grant UNMASK permission explicitly.
Hi Layan,
Please refer to this blog: Dynamic Data Masking in SQL Server
I used the data from it to do a test for you.
--create table
CREATE TABLE EmployeeData
(MemberID INT IDENTITY PRIMARY KEY,
FirstName varchar(100),
LastName varchar(100),
Email varchar(100),
Age int,
JoinDate date,
LeaveDays int
)
--insert data
INSERT INTO EmployeeData
(FirstName, LastName, Email,Age,JoinDate,LeaveDays)
VALUES
('Dinesh','Asanka','******@gmail.com',35,'2020-01-01',12),
('Saman','Perera','******@somewhere.lk',45,'2020-01-01',1),
('Julian','Soman','******@uniersity.edu.org',37,'2019-11-01',1),
('Telishia','Mathewsa','******@rose.lk',51,'2018-01-01',6)
--create the user
CREATE USER TestUser WITHOUT Login;
GRANT SELECT ON EmployeeData TO TestUser
SELECT * from EmployeeData
--add a masking rule
Alter table EmployeeData ALTER column FirstName NVARCHAR(10) MASKED WITH (FUNCTION = 'default()')
--select
EXECUTE AS USER = 'TestUser';
SELECT * FROM EmployeeData;
REVERT;
As you can see from the test result, the purpose of dynamic data masking is to limit exposure of sensitive data, preventing users who shouldn't have access to the data from viewing it. Dynamic data masking doesn't aim to prevent database users from connecting directly to the database and running exhaustive queries that expose pieces of the sensitive data.
--UNMASK
GRANT UNMASK TO TestUser
--select
EXECUTE AS USER = 'TestUser';
SELECT * FROM EmployeeData;
REVERT;
Hope this test would give you some help.
Best regards,
Seeya
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".