Sql endpoint access and DB access roles HELP!

[Eye0nine]

I'm experiencing an issue with access management for Fabric SQL endpoints. Let me first explain the scenario:

We have a Dataverse connected to an F8 Fabric capacity. A data warehouse stores facts and dimensions that reference the Dataverse for BI reporting. Some of these BI reports include paginated reports that directly query the Dataverse and contain functions.

The problem is that users with only Viewer rights cannot view these reports due to the functions within the Dataverse. I've explored all options in the Fabric UI but can't find a way to assign users to database roles via the SQL endpoint.

I've tried several approaches, including:

• Sharing the dataset with users 

• Creating a Power BI app and adding user to the audience

• Adding gateways to the connections with a service account

Despite these efforts, users still can't access the reports. I was able to create a new database security role on the SQL endpoint, but now I can't add users to it.

I also don't understand why Microsoft removed SQL users and logins, which makes managing access even more challenging.

Any insights or solutions would be greatly appreciated!

[Eye0nine]

Hi @v-tsaipranay 

Thank you for your response. I appreciate your suggestions. However, I have some concerns regarding the recommended approaches:

• Elevated Permissions Beyond Viewer:
  Our organization follows strict security policies that restrict end users to Viewer permissions only. This is to prevent unintended modifications or disruptions, as users are only trained to view reports. Granting additional permissions poses a significant risk.

• Assigning Users to Database Roles via the Fabric UI:
  The Fabric UI allows assigning only a limited set of roles, whereas I need to grant SELECT permissions.

  • T-SQL Grant Statement for Elevated Permissions:
    The Fabric documentation states:

    "The shared recipient by default receives 'Read' permission, which only allows the recipient to connect to the SQL analytics endpoint, the equivalent of CONNECT permissions in SQL Server. The shared recipient won't be able to query any table or view or execute any function or stored procedure unless they're provided access to objects within the Warehouse using the T-SQL GRANT statement."

    Based on this, it seems possible to grant elevated permissions using T-SQL, but I haven't found any examples demonstrating how to do this. Additionally, the users I have shared the Dataverse with do not appear in the SQL endpoint, so assigning them a custom role isn't an option either.

  • Granting Contributor or Member Access to the Fabric Workspace:
    Unfortunately, this is not feasible at the moment.

  • Using Service Principals or Power BI Security Groups:
    Implementing this solution would take months since service principals and security groups are managed by different teams within our organization.

  Given these constraints, do you have any alternative solutions or guidance on how to proceed?

  s.

•  

[v-tsaipranay]

Hi @Eye0nine ,

Thank you for your detailed explanation. Since Microsoft Fabric does not support direct SQL logins or database role assignments, users must be granted access through the Fabric UI first.

To allow users with Viewer permissions to query data via the SQL endpoint, consider the following:

• Fabric allows the use of T-SQL GRANT statements to provide SELECT access to specific tables or schemas, enabling granular control over data access. However, before granting these object-level permissions, users must first have access to the Warehouse in Fabric. Without this prerequisite, the GRANT statements will not take effect.
• To ensure users appear in the SQL Endpoint, verify they have the necessary Fabric workspace permissions and explicit access. Without proper permissions, users may not be recognized within the SQL environment.
• To manage permissions efficiently, use Microsoft Entra ID (Azure AD) security groups rather than assigning access to individual users. This method simplifies administration, ensures consistency, and makes user access management easier at scale.
• ⁠ If accessing the SQL endpoint is difficult, an alternative is to share a Power BI dataset. This allows users to view and analyze the data without needing direct SQL access, simplifying access management while maintaining security.

I have also included the Microsoft documentation as a reference for better understanding:

https://learn.microsoft.com/en-us/fabric/data-warehouse/security#granular-security

I hope my suggestions give you good idea, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[Eye0nine]

"Hi, based on the link you provided, I see the following:

My next question is, how can I use ALTER ROLE or GRANT in the SQL endpoint? Can I change the default role or grant SELECT on an object to the default role? Also, what exactly is the default role?"

[v-tsaipranay · Accepted solution]

Hello @Eye0nine ,

Using ALTER ROLE or GRANT in SQL Endpoint

Microsoft Fabric does not support ALTER ROLE in the SQL Endpoint. Unlike traditional SQL Server databases, role management in Fabric is handled through Microsoft Entra ID (formerly Azure AD) rather than SQL roles. However, you can use the GRANT statement to provide object-level permissions.

For 2^nd question, no the default role is not configurable within Fabric SQL Endpoints. Permissions are controlled at the workspace level through Fabric roles:

• Admin, Member, Contributor, Viewer (Read-only access).

Also for the question you asked what is the Default Role:

• If a user does not have explicit permissions, their access is determined by their workspace role (e.g., Viewer role allows read-only access).
• Object-level access can be fine-tuned using GRANT for specific users.

To manage user permissions effectively, you may need to assign workspace-level roles in Microsoft Fabric and then use GRANT for additional fine-grained control.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[v-tsaipranay]

Hi @Eye0nine ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay]

Hello @Eye0nine ,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thankyou.

[v-tsaipranay]

Hi @Eye0nine ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @Eye0nine ,

Thanks for reaching out to the Microsoft fabric community forum.

As you wanted to assign users to a SQL security role in the Fabric SQL endpoint, assuming it would allow them to execute functions in Dataverse and view paginated reports. However, Microsoft does not support direct SQL logins or database user assignments in Fabric.

As paginated reports invoke functions within Dataverse, users may require higher access levels than just "Viewer" to execute these functions successfully.

Also please consider the following:

• Certain Dataverse functions might need elevated permissions beyond Viewer.
• Assign users to database roles via the Fabric UI rather than trying to create SQL-based roles.
• Grant the users with Contributor or Member access to the Fabric workspace.
• Using service principals or Power BI security groups for controlled access.

I hope my suggestions give you good ideas, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you.