How do I limit dynamic data masking with a where clause?

Question

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.

Answer 1

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 ('[email protected]'),('[email protected]'),('[email protected]');
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.