How to Identify the list of tables to enable Row Level Security in SQL Database Tables...

In this scenario we have table as Employee and Row Level Security needs to be implemented for all the tables which are having a Foreign Key Relationship with the Employee table.
We can use the below query to identify the relevant tables where still the Row Level Security is not enabled.

SELECT
f.parent_object_id AS TableObjectId,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName
   ,sp.object_id
FROM
   sys.foreign_keys AS f
INNER JOIN
   sys.foreign_key_columns AS fc
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
   sys.tables t
      ON t.OBJECT_ID = fc.referenced_object_id
LEFT OUTER JOIN sys.security_predicates AS sp
ON f.parent_object_id=sp.target_object_id
WHERE
   OBJECT_NAME (f.referenced_object_id) = 'Employee'
   AND sp.object_id IS NULL

No comments:

Post a Comment

How to run UPDATE/INSERT/DELETE Statements on Azure SQL Database in Microsoft Fabric Notebook...

You can run UPDATE/INSERT/DELETE Statements on Azure SQL Database in Microsoft Fabric Notebook using Python SQL Driver - pyodbc.  For the Fa...