PowerShell & SQL Server: Unmask All Masked Columns (Dynamic Data Masking) In Database

Necessity is the mother of new PowerShell code :-)!

Today, I had to unmask all the columns I had helped mask using Dynamic Data Masking. This simple post assumes that you are a privileged user with the ability to drop “Column Masking”!

Identify Masked Columns

The SQL to identify all masked columns is pretty simple (from link above)

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function  
FROM sys.masked_columns AS c  
JOIN sys.tables AS tbl   
    ON c.[object_id] = tbl.[object_id]  
WHERE is_masked = 1; 

Generate “Unmask” SQL

We know from the link above that the syntax to unmask columns is

ALTER TABLE Data.Membership   
    ALTER COLUMN LastName DROP MASKED;  

The trick is to then generate the SQL to “Unmask” all the masked columns in the database (you can adjust the SQL to query specific tables/columns)

SELECT  
		  SCHEMA_NAME(tbl.schema_id) table_schema, 
		  tbl.name as table_name, c.name as table_column, c.is_masked, c.masking_function,
		  'ALTER TABLE [' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + '] ALTER COLUMN ' + c.name + ' DROP MASKED;' as UnMaskSQL
FROM sys.masked_columns AS c  
		  JOIN sys.tables AS tbl   
                    ON c.[object_id] = tbl.[object_id]  
WHERE is_masked = 1;

The results would look something like this (made-up data :-))

ALTER TABLE [Customer].[Referral] ALTER COLUMN Referral_Details DROP MASKED;
ALTER TABLE [Customer].[Referral] ALTER COLUMN Referral_Cancelled_Comment DROP MASKED;
ALTER TABLE [Customer].[Analysis] ALTER COLUMN Summary DROP MASKED;
ALTER TABLE [Customer].[Customer] ALTER COLUMN Secrets DROP MASKED;
ALTER TABLE [Inventory].[Inventory] ALTER COLUMN Price DROP MASKED;
ALTER TABLE [Inventory].[Inventory] ALTER COLUMN Discount DROP MASKED;

Run the Generated UnMask SQL

At this point, you could just copy the results, paste into SSMS and run it to unmask and be done!

Alternatively, if you want this to be part of an automated process, you could use dbatools and create a script like this (do replace the highlighted lines):

Import-Module dbatools

[string] $TargetInstance = 'MySQLServer'
[string] $TargetDB = 'MySQLDatabase'
[string] $query = "SELECT /* SCHEMA_NAME(tbl.schema_id) table_schema, tbl.name as table_name, c.name as table_column, c.is_masked, c.masking_function, */
		                'ALTER TABLE [' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + '] ALTER COLUMN ' + c.name + ' DROP MASKED;' as UnMaskSQL
                FROM sys.masked_columns AS c  
                JOIN sys.tables AS tbl   
                    ON c.[object_id] = tbl.[object_id]  
                WHERE is_masked = 1;"

[string] $sqlString = Invoke-DbaQuery -SqlInstance $TargetInstance -Database $TargetDB -Query $query -As SingleValue |
                        Out-String
Write-Verbose $sqlString
Invoke-DbaQuery -SqlInstance $TargetInstance -Database $TargetDB -Query $sqlString

Going SQL by SQL in Generated SQL Commands

I know that not all of you may want to run all the SQL as one query. You may want to traverse the results row by row and run the SQL. The below snippet should take care of it if you prefer that method.

Import-Module dbatools

[string] $TargetInstance = 'MySQLServer'
[string] $TargetDB = 'MySQLDatabase'
[string] $query = "SELECT /* SCHEMA_NAME(tbl.schema_id) table_schema, tbl.name as table_name, c.name as table_column, c.is_masked, c.masking_function, */
		                'ALTER TABLE [' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + '] ALTER COLUMN ' + c.name + ' DROP MASKED;' as UnMaskSQL
                FROM sys.masked_columns AS c  
                JOIN sys.tables AS tbl   
                    ON c.[object_id] = tbl.[object_id]  
                WHERE is_masked = 1;"

$rows = Invoke-DbaQuery -SqlInstance $TargetInstance -Database $TargetDB -Query $query -As DataRow

foreach($row in $rows){
    Write-Verbose $row.UnMaskSQL
    Invoke-DbaQuery -SqlInstance $TargetInstance -Database $TargetDB -Query $row.UnMaskSQL
}

Conclusion

I hope you found this trick useful. In fact, you can use this method to generate and run any SQL for any need using the meta-data available in the system views using PowerShell and dbatools!

Advertisement