Forum Discussion
Clarification on Access Control for SQL Endpoints in Fabric Workspaces
Hi SwedishJAS you don't have to add any permissions at the workspace level if you need granular permissions for a SQL Endpoint. To run through a scenario, if I had a user "User One" and I just wanted them to have read access on all tables in a particular SQL Endpoint I would
- Go to the Lakehouse/Warehouse and select Manage Permissions
- Select Add User and find the user/group e.g. User One
- At this stage I can select Read All SQL Endpoint Data
I could just leave this setting and click OK, then User One has access to the SQL Endoint I have granted. I don't need to add User One at the workspace level.
If I want even more granular control I can:
- Go to the Lakehouse/Warehouse and select Manage Permissions
- Select Add User and find the user/group e.g. User One
- Do not select any options in the dialog box (this provides access called CONNECT)
- Then I can go into the SQL Endpoint and issue granular permissions like GRANT SELECT ON <object> TO User/Group
Hope this helps
Can you point me towards documentation/videos that state that users need to be added to Workspaces? I have confirmed that adding users to a workspace is not required, I have tested this myself with both SSMS and browsing the OneLake catalog.
What version of SSMS are you using?
Hi SwedishJAS ,
I tried to reproduce your exact scenario and tried around various combinations of workspace roles and permissions to check how to connect to SQL endpoint without granting access to Workspace.When i gave the user Read and ReadAll permission without giving any workspace role was able to connect to SSMS without any issue, faces the same issue as you while connecting with only Read permission.
Hope this helps youDid I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to give "Kudos"
Thanks and Regards
Hi AndyDDC,
Thank you for your detailed explanation and walkthrough! Your solution was actually my first approach since it really makes sense that users can be added to the SQL Endpoint without being part of the workspace permissions.
However, I ran into the following issue when attempting to connect to the SQL Endpoint using SQL Server Management Studio (SSMS):
Error message:
After investigating further, I found that the only way to resolve this issue was to add the user to the workspace. To double-check, I reviewed the documentation, YouTube videos, and discussions, and these sources confirmed that adding the user to the workspace is required.
My question to you is: Have you verified that it actually works for you in practice to add users to the SQL Endpoint without including them in the workspace? Or did you, like me, make the assumption that it should work based on the design?
Thanks again for sharing your solution! It would be very interesting to hear if you've successfully managed to get this working without workspace permissions.
v-nmadadi-msftCommunity Support
Hi SwedishJAS ,
I tried to reproduce your exact scenario and tried around various combinations of workspace roles and permissions to check how to connect to SQL endpoint without granting access to Workspace.When i gave the user Read and ReadAll permission without giving any workspace role was able to connect to SSMS without any issue, faces the same issue as you while connecting with only Read permission.
Hope this helps youDid I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to give "Kudos"
Thanks and Regards- MarkBurgess
Advocate I
I've just had this exact issue and it was due to SSMS trying to connect to the <default> database. If you've just given access to a single lakehouse/warehouse then there isn't a default.
The solution in SSMS is: before clicking Connect, go into Options and under Connection Properties type in the name of your lakehouse.
- AndyDDC
Most Valuable Professional
Can you point me towards documentation/videos that state that users need to be added to Workspaces? I have confirmed that adding users to a workspace is not required, I have tested this myself with both SSMS and browsing the OneLake catalog.
What version of SSMS are you using?
- A big thank you to both of you—I’ll give it another shot next time. This time, I worked around the issue by creating a dedicated workspace for a SQL endpoint. I’m glad that a dedicated Fabric capacity is no longer required. 🙂
I trust your explanations, and that does seem like the most reasonable scenario. I couldn’t find the description I initially interpreted as confirming the worse situation—it might have been incorrect or outdated.
Thanks again for your quick and detailed responses!
/Jens
