0

I am interested in using SQL Server dynamic data masking on a subset of rows within a table. Is it possible to do this?

As an example, I have a table of users with email addresses. I do not want to mask records for my company's internal users. I would like to use a where clause such as where email not like '%acme.com'.

After reviewing the responses, I realized that I need to clarify further. I would like for all non-administrative users to see the same query results. When they query the users table, the resulting rows with an email of acme.com should not be masked and the resulting rows with a different email domain should be masked.

  • what do you mean by creating view? If you want to use dynamic data masking you need to add rules for each column separately in table (column) definition. – NikitaSerbskiy Jul 27 at 19:27
  • I thought I remembered reading something about a possible solution using views, but could find nothing after doing a search. As a result, I have edited my question to not refer to views. – Scott Jul 28 at 11:43
2

I don't think that this (masking only certain rows in the table) can be done with Dynamic Data Masking.
You could create a view that will do what you want to do by doing something like this:

create table email (id int identity, email varchar(100));
insert into email values ('mail1@hotmail.com'),('email2@gmail.com'),('email3@mycmp.com');
go

create view email_filter as
select case when email like '%@mycmp.com' then email 
else left(email,charindex('@', email,0)) end "email" 
from email
go

select * from email_filter;

drop table email
drop view email_filter

Note that this will not mask the data from the "email" table so if you want to make sure the users cannot access it, you will have to restrict the access.

Note also that this have some limitation. If you are expecting the view to return result for a query like :

Select * from email_filter where email like '%hotmail.com'

it will not return any row.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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