How to create a custom function for Dynamic Data Masking

Question

I'm testing Dynamic Data Masking and I discovered that SQL Server propose only 4 functions out of the box:

Function	Examples
Default	Example of alter syntax: ALTER COLUMN Gender ADD MASKED WITH (FUNCTION = 'default()')
Email	Example of alter syntax: ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')
Random	Example of alter syntax: ALTER COLUMN [Month] ADD MASKED WITH (FUNCTION = 'random(1, 12)')
Custom String	Example of alter syntax: ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')

I would like to use the last option, Custom String, because I need to:

• Keep 3 prefix number
• Shuffle 3 numbers in the middle
• Keep 3 last numbers

So this phone number 123-456-789 will become 123-039-789

My first approach is to nest two functions together this way:

ALTER TABLE [Person].[PersonPhone]
ALTER COLUMN [PhoneNumber] NVARCHAR(25) MASKED
    WITH (FUNCTION = 'partial(4,"' + (FUNCTION = 'random(100,999)') + '",4)');

How Can I execute a function inside a function?

Or is there any other option to create a custom MASK?

Answer 1

There is no option to combine two data masking functions in SQL Server (2016 or 2019).

I think you want to shuffle (instead of mask) the middle portion because you want the phone numbers to look like they have not been masked, but I don't think this is a good idea. It's better to mask them with X-s, so it is obvious what has been masked. If you have a validation rule somewhere else that says there can be no letters in the phone number, you can mask the middle-portion with 0-s, by using MASKED WITH (FUNCTION = 'partial(4,"000",4)').

The alternative would be not to use Dynamic Data Masking, but a custom view (or a computed column), where you can call any T-SQL function, for example:

LEFT(PhoneNumber,4)+REPLACE(STR(ABS(CHECKSUM(NEWID()))%1000,3),' ','0')+RIGHT(PhoneNumber,4)