How do I copy SQL Server Data Discovery and Classification rules and suppressed recommendations to another database? We have hundreds of SQL Server databases, all of which share a common schema. We are implementing Data Discovery and Classification policies. We wish to create one rule set and copy it to all like databases.
Using either SSMS or the Azure portal, it's possible to view recommendations for data classification, accept or suppress recommendations and create custom classifications.
It is well-documented that classifications are stored in sys.sensitivity_classifications and can be managed with ADD SENSITIVITY CLASSIFICATION and DROP SENSITIVITY CLASSIFICATION commands.
What is not documented as of this writing is where the suppressed recommendations are stored.
We wish to replicate the suppressed recommendations to all databases in our fleet, so that Azure Security Center will stop nagging us to finish classifying all our data.
Where are the suppressed recommendations stored? How can I copy the rules to other like databases?
I found the Data Discovery and Classification recommendation suppressions in sys.extended_properties with the property name sys_data_classification_recommendation_disabled.
Copying these extended properties to another database successfully suppresses the recommendations.
I wrote the following SQL to be run in a database in which classification rules and suppressions have already been set. It code-generates the SQL commands needed to migrate suppression rules from one database to another. The script it generates is idempotent, checking for the existence of the properties before setting them.
Do be mindful that you might have permission to read the properties, but not write them.
;with Suppress (SchemaName, TableName, ColumnName) 
AS
(
select QUOTENAME(schema_name(t.schema_id)) as SchemaName, QUOTENAME(t.Name) as TableName, QUOTENAME(c.Name) as ColumnName
from sys.extended_properties p
join sys.tables t on p.major_id = t.object_id
join sys.all_columns c on c.object_id = t.object_id and p.minor_id = c.column_id
where p.name = 'sys_data_classification_recommendation_disabled'
)
select Script =
N'IF NOT EXISTS (
    SELECT NULL
    FROM SYS.EXTENDED_PROPERTIES
    WHERE [major_id] = OBJECT_ID(''' + s.SchemaName + N'.' + s.TableName + ''')
        AND [name] = N''sys_data_classification_recommendation_disabled''
        AND [minor_id] = (
            SELECT [column_id]
            FROM SYS.COLUMNS
            WHERE [name] = ''' + s.ColumnName + '''
                AND [object_id] = OBJECT_ID(''' + s.SchemaName + N'.' + s.Tablename + ''')
            )
    )
EXEC sys.sp_addextendedproperty @name=N''sys_data_classification_recommendation_disabled'', @value=N''1'' , @level0type=N''SCHEMA'',@level0name=N''' + s.SchemaName + 
''', @level1type=N''TABLE'',@level1name=N''' + s.TableName + ''', @level2type=N''COLUMN'',@level2name=N''' + s.ColumnName + ''';'
from Suppress s

This next script code-generates the SQL commands needed to migrate the actual classification rules (not recommendation suppressions) from one database to another.
The ADD SENSITIVITY CLASSIFICATION command is blissfully idempotent by nature. You do not have to check for existence before running it a second time.
select 'ADD SENSITIVITY CLASSIFICATION TO ' + s.schema_name + '.' + s.table_name + '.' + s.column_name + ' WITH (LABEL=''' + s.label + ''', INFORMATION_TYPE=''' + s.information_type + ''', RANK=' + s.rank_desc + ');'
from
(
SELECT 
    cast(schema_name(O.schema_id) as nvarchar(200)) AS schema_name,
    cast(O.NAME as nvarchar(200)) AS table_name,
    cast(C.NAME as nvarchar(200)) AS column_name,
    cast(information_type as nvarchar(200)) as information_type,
    cast(label as nvarchar(200)) as label,
    rank,
    cast(rank_desc as nvarchar(200)) as rank_desc
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
) as s
order by s.schema_name, s.table_name, s.column_name