Microsoft Fabric Warehouse - Configure Access and Permissions

By:   |   Updated: 2024-07-02   |   Comments   |   Related: > Microsoft Fabric


We are starting a new analytics project in Microsoft Fabric, and our data will land in a warehouse. This is the first time we're using Fabric, and we are wondering about the different options for sharing access to a warehouse we developed in a workspace.


Microsoft Fabric is a centralized, Software-as-a-Service (SaaS) data analytics platform. One of the workloads in Fabric is the warehouse, where you can store, transform, and query data in an environment very similar to a SQL Server database. It's not exactly the same because, behind the scenes, all tables are stored as delta tables (which use Parquet files as the storage format). For more information about the warehouse, check this tip: What are Warehouses in Microsoft Fabric?

For this tip, we'll look at how to share access to a warehouse so a user can query the data inside it. We will focus on just giving access. (Granular permissions and inbound/outbound security, such as VPN gateways, Azure Private Link, etc., are out of scope for this tip. Please refer to the documentation for more information on those subjects.)

How to Give a User Access to a Fabric Warehouse

Let's suppose we have a user named Alice, who we want to give access to a warehouse. Fabric only supports authentication through Azure Entra ID (formerly known as Azure Active Directory), so we need to create this user in Azure Entra ID:

user in entra ID with the name Alice

When this user logs into Fabric for the first time, they might see the following message:

free fabric license assigned

It's important to note that the user doesn't have a Power BI Pro license, which isn't needed to work with Fabric items (except for Power BI items).

Initially, new users will only have access to their own workspace called My workspace:

my workspace only

Workspace Roles

To give Alice access to an existing warehouse, we can assign workspace roles. When logged in as a workspace admin, go to the workspace and click on Manage access.

manage workspace access

In the sidebar, click on Add people or groups.

add people to workspace roles

Enter the name or email of the user you want to assign to a role.

add user and assign role

You can select which role you want to assign to the user. These are the available workspace roles:

  • Viewer – the user can view all content but can't modify any of it. This is the default role.
  • Contributor – the user can view and modify all content.
  • Member – the user can view, modify, and share all content. A member can also add other members.
  • Admin – all the above and the ability to manage permissions. An admin can also delete the workspace.

The following table – taken from the Microsoft documentation – gives a high-level overview of the differences between the roles:

overview of capabilities per role

For a more detailed description of the roles, check out Roles in workspaces in Microsoft Fabric. When Alice is added to the Viewer role, she can now access the workspace and query all objects.

alice can query data within the warehouse

From the warehouse context menu (click on the ellipsis next to the name), the user can get the connection string to the warehouse and connect using other tools such as SQL Server Management Studio (SSMS).

get sql connection string from the context menu

She can use her email to log in (keep in mind SQL authentication is not supported):

log into Fabric warehouse using SSMS

Alice can see all warehouses (and SQL analytical endpoints) in the workspace and query their tables:

query in SSMS

However, she only has read-only access. Modifying data is not permitted.

update statement failed in SSMS

Assigning Alice a Viewer role on the workspace was perhaps a bit too broad, as all objects can be viewed and queried. Let's find out if there's an option to grant access only to a single warehouse.

Let's remove Alice from the Viewer role (using the same Manage access pane as before). If Alice was still logged into Fabric, it seems she can view the different objects in the workspace but not access them.

alice can still view the items,  but not query them

The workspace is no longer accessible only after Alice has signed out and logged in again.

Share the Warehouse with a User

Sharing is a better option than assigning the user to a workspace role if you want to give a user access to only a single item in a workspace. As an admin or member, go to the warehouse in the workspace, open the context menu, and click Share.

share an item in the fabric workspace

In the pop-up, add Alice as the recipient.

grant people access

Three permissions can be assigned:

  • Read all data using SQL: This is the ReadData permission, equivalent to the db_datareader role in SQL Server.
  • Read all OneLake data: This is the ReadAll permission, and it used to be called "read all data using Apache Spark." With this permission, the user can read the underlying Parquet files of the warehouse tables stored in OneLake.
  • Build reports on the default semantic model: This is the Build permission, which allows the user to build Power BI reports on the default model of the warehouse. This permission is selected by default.

If all permissions are deselected, the user only gets the Read permission, which is the ability to connect to the warehouse, similar to the CONNECT permission in SQL Server. When the warehouse is shared, the user will get an email with a notification (if this option wasn't deselected from the menu):

email notification after sharing

The link provides access to the OneLake hub, where you can find the connection string to the warehouse and some quick actions:

onelake hub for the warehouse

You can also find a link to the default semantic model:

link to default semantic model

It's possible you'll get some pop-up to start a free Pro trial along the way. Because the warehouse itself is directly shared, Alice can't see the workspace in the list:

workspace that hosts the warehouse is not visible in the list

The only way to find the warehouse is through the OneLake data hub. When opening the warehouse, Alice can view and query the tables (if at least the ReadData permission is assigned):

alice can view and query tables

However, modifying the data is not possible:

updating data is not allowed

Using the connection string (found in the OneLake hub), Alice can connect to the warehouse using other tools like SSMS. Don't forget to enter the database name in the connection properties:

enter database name in the connection properties

If you don't, you'll get an error stating the login failed:

login failed for user token-identified principal

After logging into the warehouse service, Alice can only see warehouses that have been shared with her:

Alice can only see warehouses that have been shared with her

When Alice was assigned the workspace Viewer role, she could see all warehouses and SQL Analytical endpoints, but now she can only see one warehouse.

If, at some point, we want to revoke the permissions shared with Alice or extend them, we can do this in the Manage Permissions pane, which can be found through the context menu:

manage permissions of warehouse

Here, we can find a list of all users who have access to the warehouse and their roles and permissions:

list of users and permissions

By clicking on the ellipsis next to a user, we can either remove permissions, add missing permissions, or remove access altogether:

edit permissions

Alice can read all the data on all the tables of the warehouse.

In the next tip, we'll dive deeper into the security options of the warehouse to further restrict access by using row-level security and column-level security.

Next Steps
  • If you want to get started with Fabric, you can try a free trial.
  • An overview of all Fabric tips can be found in this overview.
  • Stay tuned for more tips on security in Microsoft Fabric!

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-07-02

Comments For This Article

get free sql tips
agree to terms