Edit

Share your warehouse and manage permissions

  • Article

Applies to: Warehouse and Mirrored Database in Microsoft Fabric

Sharing is a convenient way to provide users read access to your Warehouse for downstream consumption. Sharing allows downstream users in your organization to consume a Warehouse using SQL, Spark, or Power BI. You can customize the level of permissions that the shared recipient is granted to provide the appropriate level of access.

Note

You must be an admin or member in your workspace to share a Warehouse in Microsoft Fabric.

Get started

After identifying the Warehouse you would like to share with another user in your Fabric workspace, select the quick action in the row to Share a Warehouse.

The following animated gif reviews the steps to select a warehouse to share, select the permissions to assign, and then finally Grant the permissions to another user.

An animated gif showing interaction with the Fabric portal where a user shares a warehouse in Microsoft Fabric with another user.

You can share your Warehouse from the OneLake Data Hub or the Synapse Data Warehouse by choosing Share from quick action, as highlighted in the following image.

Screenshot showing how to share a warehouse in the OneLake Data Hub page.

Share a Warehouse

You are prompted with options to select who you would like to share the Warehouse with, what permission(s) to grant them, and whether they will be notified by email. When you have filled in all the required fields, select Grant access.

Here's more detail about each of the permissions provided:

  • If no additional permissions are selected - 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 will not be able to query any table or view or execute any function or stored procedure unless they are provided access to objects within the Warehouse using T-SQL GRANT statement.

Note

ReadData, ReadAll, and Build are separate permissions that do not overlap.

  • "Read all data using SQL" is selected ("ReadData" permissions)- The shared recipient can read all the database objects within the Warehouse. ReadData is the equivalent of db_datareader role in SQL Server. The shared recipient can read data from all tables and views within the Warehouse. If you want to further restrict and provide granular access to some objects within the Warehouse, you can do this using T-SQL GRANT/REVOKE/DENY statements.

    • In the SQL analytics endpoint of the Lakehouse, "Read all SQL Endpoint data" is equivalent to "Read all data using SQL".

  • "Read all data using Apache Spark" is selected ("ReadAll" permissions)- The shared recipient has read access to the underlying parquet files in OneLake, which can be consumed using Spark. ReadAll should be provided only if the shared recipient wants complete access to your warehouse's files using the Spark engine.

  • "Build reports on the default dataset" checkbox is selected ("Build" permissions)- The shared recipient can build reports on top of the default semantic model that is connected to your Warehouse. Build should be provided if the shared recipient wants Build permissions on the default semantic model, to create Power BI reports on this data. The Build checkbox is selected by default, but can be unchecked.

When the shared recipient receives the email, they can select Open and navigate to the Warehouse Data Hub page.

A screenshot showing the shared user's email notification of a shared warehouse.

Depending on the level of access the shared recipient has been granted, the shared recipient is now able to connect to the SQL analytics endpoint, query the Warehouse, build reports, or read data through Spark.

A screenshot from the Fabric portal showing the 'See what already exists' page of the shared warehouse.

ReadData permissions

With ReadData permissions, the shared recipient can open the Warehouse editor in read-only mode and query the tables and views within the Warehouse. The shared recipient can also choose to copy the SQL analytics endpoint provided and connect to a client tool to run these queries.

For example, in the following screenshot, a user with ReadData permissions can query the warehouse.

A screenshot from the Fabric portal show a user can query a shared warehouse.

ReadAll permissions

A shared recipient with ReadAll permissions can find the Azure Blob File System (ABFS) path to the specific file in OneLake from the Properties pane in the Warehouse editor. The shared recipient can then use this path within a Spark Notebook to read this data.

For example, in the following screenshot, a user with ReadAll permissions can query the data in FactSale with a Spark query in a new notebook.

A screenshot from the Fabric portal where a user opens a Spark notebook to query the Warehouse shortcut.

Build permissions

With Build permissions, the shared recipient can create reports on top of the default semantic model that is connected to the Warehouse. The shared recipient can create Power BI reports from the Data Hub or also do the same using Power BI Desktop.

For example, in the following screenshot a user with Build permissions can start to Auto-create a Power BI report based on the shared warehouse.

An screenshot showing interaction with the Fabric portal, where a user can autocreate a report on the shared warehouse.

Manage permissions

The Manage permissions page shows the list of users who have been given access by either assigning to Workspace roles or item permissions.

If you are an Admin or Member, go to your workspace and select More options. Then, select Manage permissions.

Screenshot showing a user selecting Manage permissions in the warehouse context menu.

For users who were provided workspace roles, it shows the corresponding user, workspace role and permissions. Admin, Member and contributors have read/write access to items in this workspace. Viewers have ReadData permissions and can query all tables and views within the Warehouse in that workspace. Item permissions Read, ReadData, and ReadAll can be provided to users.

Screenshot showing the Manage permissions page of the Warehouse in the Fabric portal.

You can choose to add or remove permissions using Manage permissions:

  • Remove access removes all item permissions.
  • Remove ReadData removes the ReadData permissions.
  • Remove ReadAll removes ReadAll permissions.
  • Remove build removes Build permissions on the corresponding default semantic model.

Screenshot showing a user removing the ReadAll permission of a shared recipient.

Limitations

  • If you provide item permissions or remove users who previously had permissions, permission propagation can take up to two hours. The new permissions will reflect in "Manage permissions" immediately. Sign in again to ensure that the permissions are reflected in your SQL analytics endpoint.
  • Shared recipients are able to access the Warehouse using owner's identity (delegated mode). Ensure that the owner of the Warehouse is not removed from the workspace.
  • Shared recipients only have access to the Warehouse they receive and not any other items within the same workspace as the Warehouse. If you want to provide permissions for other users in your team to collaborate on the Warehouse (read and write access), add them as Workspace roles such as "Member" or "Contributor".
  • Currently, when you share a Warehouse and choose Read all data using SQL, the shared recipient can access the Warehouse editor in a read-only mode. These shared recipients can create queries, but cannot currently save their queries.
  • Currently, sharing a Warehouse is only available through the user experience.
  • If you want to provide granular access to specific objects within the Warehouse, share the Warehouse with no additional permissions, then provide granular access to specific objects using T-SQL GRANT statement. For more information, see T-SQL syntax for GRANT, REVOKE, and DENY.
  • If you see that the ReadAll permissions and ReadData permissions are disabled in the sharing dialog, refresh the page.
  • Shared recipients do not have permission to reshare a Warehouse.
  • If a report built on top of the Warehouse is shared with another recipient, the shared recipient needs more permissions to access the report. This depends on the mode of access to the semantic model by Power BI:
    • If accessed through Direct query mode then ReadData permissions (or granular SQL permissions to specific tables/views) need to be provided to the Warehouse.
    • If accessed through Direct lake mode, then ReadData permissions (or granular permissions to specific tables/views) need to be provided to the Warehouse. Direct Lake mode is the default connection type for semantic models that use a Warehouse or SQL analytics endpoint as a data source. For more information, see Direct Lake mode.
    • If accessed through Import mode then no additional permissions are needed.
    • Currently, sharing a warehouse directly with a SPN is not supported.

Data protection features

Microsoft Fabric data warehousing supports several technologies that administrators can use to protect sensitive data from unauthorized viewing. By securing or obfuscating data from unauthorized users or roles, these security features can provide data protection in both a Warehouse and SQL analytics endpoint without application changes.

  • Column-level security prevents unauthorized viewing of columns in tables.
  • Row-level security prevents unauthorized viewing of rows in tables, using familiar WHERE clause filter predicates.
  • Dynamic data masking prevents unauthorized viewing of sensitive data by using masks to prevent access to complete, such as email addresses or numbers.

Related content