Dynamic Data Masking Using SQL Endpoint and One Lake Security

Hi lali1409,  

I think your two workspaces are behaving differently because their SQL endpoints are in different OneLake access modes.

• WS1 was effectively using Delegated identity (SQL-layer permissions apply).
• WS2 is/was in User’s identity (OneLake Security governs table reads). In this mode, GRANT/REVOKE on tables is blocked, which is why you saw the “external policy action … Rows/Select” denial and the “warehouse is in user’s identity mode” message. See Microsoft’s explanation of the two modes and limitations. OneLake Security for SQL endpoints (Preview), OneLake access control model

• If you want Dynamic Data Masking (DDM) to control who sees unmasked values, you must run the SQL endpoint in Delegated identity and use T-SQL to grant UNMASK. DDM is a SQL feature, not a OneLake Security feature. DDM in Fabric warehousing, OneLake Security (DDM note)
• Working recipe for WS2 (recommended):
  1. Keep the SQL endpoint in Delegated identity.
  2. Ensure each user can connect (Workspace Viewer or share the item to give “Read/ReadData” as needed). Share & manage Warehouse permissions
  3. Run T-SQL in the SQL endpoint:

     -- (Optional) Create contained user from Entra ID
     CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
     CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
     
     -- Allow both to read the table(s)
     GRANT SELECT ON SCHEMA::dbo TO [[email protected]], [[email protected]];
     
     -- Only user1 may see real values (unmasked)
     GRANT UNMASK ON SCHEMA::dbo TO [[email protected]];
     -- Don't grant UNMASK to user2 (they’ll keep seeing masked)

     UNMASK can be granted at database, schema, table, or column scope. DDM permissions & UNMASK

• If you insist on User’s identity (OneLake Security) mode, understand that DDM isn’t managed there. You cannot “grant unmask” via OneLake roles. Your options are:
  • Use Column-Level Security (CLS) to hide sensitive columns for most users and present a SQL view that returns a masked projection for them; grant SELECT on the view (GRANTs to views work in both modes). Mode comparison (tables vs views), CLS in OneLake Security
  • Or switch the endpoint to Delegated identity for that item and stick with native DDM.

I’d start by keeping WS2 in Delegated identity and managing DDM purely with SQL-this aligns with what worked in WS1 and keeps your UNMASK logic simple and auditable.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.