Automated check of Row-Level Security policy at SQL Endpoint

Hi hominid ,
Thanks for posting in Microsoft Fabric Community.
To make sure your Row-Level Security (RLS) policy is working correctly, here’s what you can do:

1. Check if the RLS Policy is Active: Run this SQL query to see if your RLS policy is enabled:

   sql:

   SELECT 
   name AS PolicyName,
   object_id AS PolicyID,
   is_enabled AS IsEnabled
   FROM 
   sys.security_policies
   WHERE 
   name = 'Your_RLS_Policy_Name';
   
   

   If the policy is active, it will show IsEnabled = 1. If it’s missing or turned off, it will show 0 or no results.

2. Set Up Alerts to Monitor the Policy: You can set up automatic checks to monitor your policy and get alerts:

   Using Power Automate: Here’s how to create a flow to automatically check your RLS policy status:

   1. Create a New Flow:In Power Automate, select Create and choose Scheduled flow.

   2. Set Flow Schedule:Set a schedule for how often you want to check the RLS policy status (e.g., hourly or daily or weekly).

   3. Add SQL Server Action:Add a SQL Server action and select Execute a SQL query V2.
      Connect your SQL database (where your policy is stored) and paste the RLS policy check query.

   4. Add Condition to Check Policy Status:After running the query, use a Condition to check if the policy’s IsEnabled value is 1. If not, proceed to send an alert.

   5. Send an Alert:If the policy is not enabled, use an action like Send an email (V2) to notify you (or the appropriate person) that the RLS policy is missing or inactive.
      
      Using Data Activator: If Data Activator is available in your capacity, you can monitor changes to the RLS policy and automatically trigger alerts when the policy is modified or disabled.

      
      Please let us know if you need assistance with any step or need guidance for Data Activator.
      
      If this post helps, please consider accepting as solution to help others find easily and a kudos would be appreciated.

      Best regards,
      Vinay