Last active
September 10, 2024 17:01
-
-
Save JosiahSiegel/27138d927bf97087319097a3ddb0b646 to your computer and use it in GitHub Desktop.
Dynamic Data Masking Automation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment