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
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