One of our customers came up with a requirement where they wanted to tag all the columns in Azure SQL DB for Azure data Classification .
Currently, in the data classification module through Azure portal, you can use pattern to tag certain keywords with the specific classification (credit card, personal information etc) and you can Accept selected recommendations as well as you can add your own pattern.
For example: We can see below the default recommendation which is adding sensitive data to the confidential label.
However if you want to add all the columns which exist in the database and doesn't qualify in any of the pattern, you need to do it manually by adding the column in the portal.
Doing the activity manually for hundreds of columns can be cumbersome task. To overcome this, you can use below T-SQL script where we can add all the remaining columns in the database to the specific sensitivity label.
For example: For the rest of the columns which is not in any of the pattern are the columns not having any sensitive data and we will put that in General sensitivity level.
SET NOCOUNT ON
DECLARE dataclassification CURSOR FOR
SELECT SCHEMA_NAME(schema_id) AS schema_name, t.name AS table_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
where c.name not in ( SELECT
C.NAME AS column_name
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)
DECLARE @tableSchema NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @Columnname NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)
FETCH NEXT FROM dataclassification INTO @tableSchema, @tableName, @columnname
WHILE (@@FETCH_STATUS = 0)
SET @Statement = 'ADD SENSITIVITY CLASSIFICATION TO ' + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + '.' + '[' + @Columnname + ']'
+ 'WITH ( LABEL=''General'' , Information_type =''Other'')' --Here you can specify the specific senstivity label which you want your column to get added.
PRINT @Statement -- comment this print statement to prevent it from printing whenever you are ready to execute the command below.
--EXEC sp_executesql @Statement -- remove the comment on the beginning of this line to run the commands
FETCH NEXT FROM dataclassification INTO @tableSchema, @tableName,@columnname
SET NOCOUNT OFF
After executing the above query, we can see that it has added the rest of the columns in the database to general sensitivity level .
We can verify the same on Azure Portal as we can now see all the columns added in the General sensitivity level.
Thanks to @RoshnaNazir for helping me in authoring this blog.
References : -