Masking Certain Parts of Specific Records

Question

Is there a way in SQL Server to mask only certain records for a given column based on a primary key value? I know the new Dynamic Data Masking function automatically masks the whole field that you select. Is there a way to just do certain records?

Answer 1

If you control the T-SQL (like if you're dealing with views or a reporting system), then you're probably looking for a user-defined function. With UDFs, you can code your own logic into something like this:

SELECT dbo.fn_Mask(MySecretField) FROM dbo.tbl_PreciousData

(Having a little fun with the naming geeks there.)

Then, inside fn_Mask, you'd write your own logic to take MySecretField as your input (and you could pass in multiple fields if you like), and even look at the logged-in user's security information to decide whether or not to mask the data.

But danger, Will Robinson: scalar UDFs make your query go serial, as opposed to parallel. Between that, and the fact that people can still directly select MySecretField, this is only a solution for limited scenarios.