1

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?

4

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
2
  • This is really useful. I suggest you mark it as the answer to help others if possible – Martin Cairney Feb 5 at 5:38
  • Thank you! I absolutely will mark it as the answer, as soon as two days pass. It won't let me do it sooner. ¯_(ツ)_/¯ – Larry Silverman Feb 5 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.