Forum Discussion
Helper IOneLake Security RLS works in Semantic Model, but returns 0 rows in SQL Endpoint
I was able to identify the root cause. In my case, some Entra ID groups had been renamed. Although the object IDs remained the same, the SQL analytics endpoint still had stale principal entries under the old names. Running a diagnostic query against sys.database_principals helped me identify the external principals present in the SQL endpoint, which is what that catalog view returns.
After identifying the affected principal, I removed it by name from the current database and then triggered a metadata sync. The DROP USER syntax for a name with spaces must use brackets, for example DROP USER [Group Name]
After that, the sync completed successfully and I was able to query the table normally again.
Super UserHi BIscoverer,
My last stab at this is asking if you have "shared" the lakehouse/sql endpoint with your users?
https://learn.microsoft.com/en-us/fabric/onelake/security/sql-analytics-endpoint-onelake-security
The SQL Analytics Endpoint requires a one-to-one mapping between item permissions and members in a OneLake security role to sync correctly. If you grant an identity access to a OneLake security role, that same identity needs to have Fabric Read permission to the lakehouse as well. For example, if a user assigns "[email protected]" to a OneLake security role then "[email protected]" must also be assigned to that lakehouse.
Helper IHi tayloramy ,
Thanks again for the suggestion.
Yes, I already granted Fabric Read access to the same Entra ID groups that are assigned to the OneLake security roles, so the role membership and item permission mapping should be aligned. I also removed the admin group from my custom full-access role and deleted that role entirely, but I still see the same stale auto-generated roles in the SQL endpoint, the same sync errors, and the table still returns 0 rows there, while the semantic model continues to work correctly.
At this point I’ve submitted a Microsoft support ticket as this does not look like expected behavior anymore. I’ll share an update here once I learn more.