Tuesday, January 29, 2019

Check GDPR Compliancy in SQL Server with Dynamic Masking and Encryption

SELECT S.name AS schema_name,
       T.name AS table_name,
       C.name AS column_name,
       TY.name AS type_name,
       COALESCE(IT.value, N'') AS information_type,
       COALESCE(SL.value, N'') AS sensitivity_label,
          COALESCE(mc.is_masked, '0') as IsMasked,
          ISNULL(c.encryption_type, 0) as IsEncrypted
FROM sys.schemas AS S
    JOIN sys.tables AS T
        ON T.schema_id = S.schema_id
    JOIN sys.columns AS C
        ON C.object_id = T.object_id
    JOIN sys.types AS TY
        ON TY.user_type_id = C.user_type_id
    LEFT OUTER JOIN sys.extended_properties AS IT
        ON IT.major_id = C.object_id
           AND IT.minor_id = C.column_id
           AND IT.name = 'sys_information_type_name'
    LEFT OUTER JOIN sys.extended_properties AS SL
        ON SL.major_id = C.object_id
           AND SL.minor_id = C.column_id
           AND SL.name = 'sys_sensitivity_label_name'
       LEFT OUTER JOIN sys.masked_columns as mc
             ON mc.object_id = t.object_id
             AND mc.column_id = c.column_id
where it.value is not null
ORDER BY S.name,
         T.name,
         C.name;

No comments:

Post a Comment