How to export Dynamic Data Masking

PROBLEM

One day or another you arrive to the point where you have to mask your database.

You pass days discovering, classifying and masking and then you realize that the masking… cannot be exported to another database.

You cry boiling tears and you beg your manager to hire a slave intern in order to escape the tedious task.

…look no further because there is a…

SOLUTION

What the hack, Microsoft, multi-tenant architecture are the norm nowadays, I cannot believe there is no deployment for data masking… sgrunt!

SELECT

schema_name(tbl.schema_id) AS schema_name,

tbl.name as table_name,

mc.name AS column_name,

mc.is_masked

, =

CASE

WHEN tp. IN (‘varchar’, ‘char’) THEN tp. + ‘(‘ + IIF(mc.max_length = -1, ‘max’, CAST(mc.max_length AS VARCHAR(25))) + ‘)’

WHEN tp. IN (‘nvarchar’,’nchar’) THEN tp. + ‘(‘ + IIF(mc.max_length = -1, ‘max’, CAST(mc.max_length / 2 AS VARCHAR(25)))+ ‘)’

WHEN tp. IN (‘decimal’, ‘numeric’) THEN tp. + ‘(‘ + CAST(mc. AS VARCHAR(25)) + ‘, ‘ + CAST(mc. AS VARCHAR(25)) + ‘)’

WHEN tp. IN (‘datetime2’) THEN tp. + ‘(‘ + CAST(mc. AS VARCHAR(25)) + ‘)’

ELSE tp.

END,

mc.masking_function

,’ALTER TABLE ‘

+ schema_name(tbl.schema_id) + ‘.’ + tbl.name +

‘ ALTER COLUMN ‘

+ mc.name + ‘ ‘ +

CASE

WHEN tp. IN (‘varchar’, ‘char’) THEN tp. + ‘(‘ + IIF(mc.max_length = -1, ‘max’, CAST(mc.max_length AS VARCHAR(25))) + ‘)’

WHEN tp. IN (‘nvarchar’,’nchar’) THEN tp. + ‘(‘ + IIF(mc.max_length = -1, ‘max’, CAST(mc.max_length / 2 AS VARCHAR(25)))+ ‘)’

WHEN tp. IN (‘decimal’, ‘numeric’) THEN tp. + ‘(‘ + CAST(mc. AS VARCHAR(25)) + ‘, ‘ + CAST(mc. AS VARCHAR(25)) + ‘)’

WHEN tp. IN (‘datetime2’) THEN tp. + ‘(‘ + CAST(mc. AS VARCHAR(25)) + ‘)’

ELSE tp.

END

+ ‘ MASKED WITH (FUNCTION = ‘’’

— + mc.masking_function

+ CAST(mc.masking_function COLLATE Latin1_General_CI_AI AS nvarchar(100))

+ ‘’’);’

FROM sys.masked_columns AS mc

JOIN sys.tables AS tbl ON mc. = tbl.

JOIN sys.types tp ON tp.user_type_id = mc.system_type_id

WHERE mc.is_masked = 1;

Now copy the last column and deploy like there is no tomorrow

Export Dynamic Data Masking

Keep in mind that your data masking deployment might fail if there are constraints:

Msg 5074, Level 16, State 1, Line 1

The object ‘PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID’ is dependent on column ‘PhoneNumber’.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE ALTER COLUMN PhoneNumber failed because one or more objects access this column.

I still have to implemented a logic that allows the constraint to be copied, removed and then applied again.

While working on the sys.masked_columns I also find out that while the table uses the SQL_Latin1_General_CP1_CI_AS collation the column masking_function uses the collation Latin1_General_CI_AS_KS_WS and this looks like a bug to me

CONCLUSION

I presented my work to one of my managers and I could see the WOW on his face.

I’m still astonished when I see that this useful tool is mostly unknown.The reports are colourful and can be exported in PDF, Excel and Word. The automatic Data Classification with suggestions that cover most of the languages are a genius idea.

So sad that this tool hasn’t been updated since his release.

Lorem Ipsum “Neque porro quisquam est qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit…” “There is no one who loves pain itself, who seeks