Forum Discussion
SELECT permissions or external policy action error. OneLake Security issue
Hello
I am trying the OneLake security on a lakehouse has shortcut tables. I had created a security role in OneLake security that can only read one table from it and i had added a user to it. In addition in the sql endpoint of the lakehouse i did flipp the OneLake Access mode to User's identity instead of Delegated identity.
In addition i had granted the user the Read to the lakehouse, and Read, ReadData, ReadAll to the sql endpoint.
The user is able to see the table and its content when in the lakehouse mode.
However when he tries to check the table from the sql endpoint mode, it is telling him that the Warehouse is empty and no table appear in the Schema/dbo. When he tries to query it using a select * from table, he is getting the following error:
SELECT permissions or external policy action ‘Microsoft.sql/sqlservers/databases/Schemas/Tables/Rows/Select’ was denied
What makes things even stranger is that a report created on the lakehouse using the new semantic model feature in power bi services, and then creating a report linked to the semantic model seems to restrict what visuals the user is seeing. Any visual using any data point from a table other than the table he has access to is failing.
In power bi desktop if he tries to connect to the lakehouse using any of the options 'connect to onelake' or connect to the sql endpoint is not displaying any table in the pop window.
What i am looking originally is a method to restrict the user of a developer to a subset of tables in a lakehouse, on which they can connect to from power bi or sql endpoint.
Not sure if it is a bug or am i missing something in the configuration that is causing this behaviour.
Any help is appreciated.
Hi mkjit256,
Despite the Fabric documentation clearly supporting this scenario in principle, configuring access in SQL Endpoint via OneLake Security is still maturing. For your use case, it would be best to limit user access in:
- Lakehouse: using OneLake Security
- SQL Endpoint: Grant/Deny T-SQL statements (switch back to Delegated Identity mode)
This approach works like a charm!
5 Replies
Hi mkjit256,
Despite the Fabric documentation clearly supporting this scenario in principle, configuring access in SQL Endpoint via OneLake Security is still maturing. For your use case, it would be best to limit user access in:
- Lakehouse: using OneLake Security
- SQL Endpoint: Grant/Deny T-SQL statements (switch back to Delegated Identity mode)
This approach works like a charm!
Thanks for your reply. I will give it a try and update
Thank you , i did exactly what you had suggested and it worked like a Charm. Summarizing the implemented solution for others in case needed.
In the SQL Endpoint mode of the lakehouse, set the OneLake access mode to Delegated.
At the level of the sql endpoint the only permission granted was Read. (ReadData and ReadAll will expose all the tables).
If you want to for example to grant access of sales table to a role called salesreader:
Start by creating the role
create role salesreader; goTo grant access to the sales table :
GRANT SELECT ON OBJECT::dbo.sales TO [salesreader]To add a member to the role you use:
ALTER ROLE [salesreader] add member [[email protected]]and that should do it
Hi mkjit256
Thank you for reaching out to the Microsoft Fabric community forum.
The behavior you are experiencing is normal for Microsoft Fabric. OneLake security works at the storage layer, controlling access to files and folders in Delta tables, but it doesn't affect SQL permissions for the Lakehouse SQL analytics endpoint.
The SQL endpoint manages its own authorization separately. With User’s identity access mode, the SQL engine checks each user's identity, and OneLake table-level roles don’t map to SQL permissions or metadata visibility. This means a user might be able to read a table in Lakehouse (Spark/OneLake), but still see an empty schema or get a “SELECT permissions or external policy action was denied” error when using the SQL endpoint.
Power BI enforces the same rules. Direct Lake semantic models and Power BI Desktop depend on SQL metadata and authorization, so visuals or connections will fail if the user lacks the necessary SQL access, even if they have OneLake storage access. This reflects the current Microsoft Fabric security design and isn't due to a bug or configuration issue.
For more details, please refer to the Microsoft official documentation on
https://learn.microsoft.com/en-us/fabric/onelake/security/get-started-security
https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-sql-analytics-endpoint#security
https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-sql-analytics-endpoint.
If you have any more questions, please let us know and we’ll be happy to help.
Regards,
Microsoft Fabric Community Support Team.
Thanks for your reply, so if the developer wants to connect the table he is permitted to see in his onelake security role from power bi desktop (to create a report), who would he be able to do that? This is really the aim.
