Forum Discussion
Column level security with SQL analytics endpoint
Hi yongshao
Sorry for the late response.
After Granting the access to the columns ask the user to query which was not granted the access for that column.
If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.
Hi yongshao
Implementing CLS in Microsoft Fabric's Lakehouse via the SQL analytics endpoint involves a series of steps to ensure that users have access only to the specific columns they're permitted to view.
Here's a detailed, step-by-step guide to help you set up CLS effectively:
-
Open your Lakehouse in Microsoft Fabric and switch to the SQL analytics endpoint.
-
Optionally, create a role using CREATE ROLE [RoleName]; and add users with ALTER ROLE [RoleName] ADD MEMBER [UserEmail];
-
Grant access to specific columns using GRANT SELECT ([Column1], [Column2]) ON dbo.YourTable TO [RoleName];
-
Test with a user account to confirm only permitted columns are visible using a simple SELECT query.
-
Reapply permissions after syncing tables, as security settings may be reset.
-
Ensure users are authenticated through Microsoft Entra ID for CLS to be enforced.
-
Be aware that Power BI may switch to Direct Query mode to honor CLS settings.
If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.
v-csrikanth Thanks for your response - it's helpful
Test with a user account to confirm only permitted columns are visible using a simple SELECT query.
How to test? grant the user to access the lakehouse first? otherwise, how the user connects the sql endpoint and test?
if granting the user as lakehouse viewer, such CLS doesn't take any effect - tested
