Dynamic Data Masking Automation

ddm_auto.sql

/*
EXECUTE AS USER = 'non-admin-user';

SELECT *
FROM masked_table

REVERT;
*/

DECLARE 
@drop_mask BIT = 0,
@print_only BIT = 1

DECLARE @loop_num INT = 1
DECLARE @temp_cmd
TABLE (row_num int IDENTITY (1, 1) Primary key NOT NULL, cmd NVARCHAR(MAX) NOT NULL)

INSERT INTO @temp_cmd (cmd)
SELECT
    'ALTER TABLE ' + QUOTENAME(schema_name(O.schema_id)) + '.' + QUOTENAME(O.NAME) + 
    ' ALTER COLUMN ' + QUOTENAME(C.NAME) + IIF(@drop_mask = 0, ' ADD MASKED WITH ', ' DROP MASKED;') +
    IIF(@drop_mask = 0,
    CASE t.name
        WHEN 'numeric' THEN '(FUNCTION = ''random(' + sc.label + ', ' + sc.label + ')'');'
        WHEN 'varchar' THEN '(FUNCTION = ''partial(0, "' + sc.label + '", 0)'');'
        WHEN 'date' THEN '(FUNCTION = ''default()'');'
        ELSE '(FUNCTION = ''default()'');'
    END, '') AS [cmd]
FROM sys.sensitivity_classifications sc
    JOIN sys.objects O ON  sc.major_id = O.object_id
	JOIN sys.columns C ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id
	JOIN sys.types AS [t] ON (c.user_type_id = t.user_type_id)
WHERE sc.label <> 'NO_DEID'

WHILE (@loop_num <= (SELECT MAX(row_num) FROM @temp_cmd))
BEGIN
    DECLARE @cmd NVARCHAR(MAX) = (SELECT [cmd] FROM @temp_cmd WHERE row_num = @loop_num)
    PRINT 'loop: ' + CAST(@loop_num AS VARCHAR) + ', cmd: ' + @cmd
    IF (@print_only = 0)
        EXEC sp_executesql @cmd
    SET @loop_num+=1
END