Manage access for SQL databases in Microsoft Fabric with workspace roles and item permissions
SQL database in Microsoft Fabric, currently in preview, supports two different sets of controls that allow you to manage access for your databases: Microsoft Fabric access controls and SQL native access controls. Each control set has different properties and supports different access management scenarios and use cases. This article explains how Fabric access controls – workspace roles and item permissions – work in SQL database, how to configure them, and what you can use them for. We’ll discuss SQL native access controls in a separate blog post.
You can catch up on the overall public preview announcement for SQL database in Microsoft Fabric by reading Announcing SQL database in Microsoft Fabric Preview.
Workspace roles and item permissions
Microsoft Fabric access controls consist of workspace roles and item permissions.
Workspace roles are used to control access to all items within a workspace. There are four workspace roles: Admin, Member, Contributor, and Viewer. A Fabric administrator can add and remove members of workspace roles using the Manage access experience.
Item permissions control access to individual items, e.g., a specific SQL database or a data warehouse in the workspace. Each workspace role maps to a subset of item permissions. Therefore, adding a user to a role grants the user all permissions, mapped to the role, to all items in the workspace.
Some item types, including SQL database, allow you to manage item permissions for individual items using the Manage permissions and Share quick actions in the Microsoft Fabric portal.
Item permissions applicable to SQL databases
In general, different item types support different subsets of item permissions. We’ll now dive into item permissions that are applicable to SQL database items. What makes discussing this topic challenging is the fact that when you create a SQL database item, Microsoft Fabric generates two other items that show up under the database in the portal: a SQL analytics endpoint and the default semantic model. Both these generated items encapsulate the data, the SQL database mirrors to OneLake. Each item in the hierarchy, shown below, has different permissions.
In this article, we primarily focus on item permissions that apply to (and can be granted/revoked for) the main (top-level) SQL database items:
- Read – allows reading database properties in Microsoft Fabric portal (the control plane) and connecting to the database, e.g., using SQL Server Management Studio or an application.
- ReadAll – permits accessing data that is mirrored to OneLake directly from OneLake files. This permission has no meaning inside the database (it’s not interpreted by the SQL Database Engine.)
- ReadData – permits read access to user tables and views, and the metadata inside the database. Principals with this permission can run SELECT Transact SQL queries on any table or a view in the database.
- Reshare – allows managing permissions to the item for other users. This permission has no meaning inside the database (it’s not interpreted by the SQL Database Engine.)
- Write – grants full administrative access to the database item, both in Microsoft Fabric portal and inside the database. Principals with this permission have access to the entire surface area of the SQL database and can run arbitrary Transact-SQL statements.
The generated SQL analytics endpoint has the same applicable permissions as the top-level SQL database item, and their meaning is similar. Keep in mind, however, that the SQL analytics endpoint encapsulates read-only data that gets mirrored to OneLake. As a result, you cannot update that data even if you have the Write item permission. You can learn more about SQL analytics endpoint permissions by reviewing Security for data warehousing – Microsoft Fabric | Microsoft Learn.
The permissions applicable to the generated default semantic models are: Build, Read, Reshare, and Write, and you can read about them in this article: Semantic model permissions – Power BI | Microsoft Learn.
How to manage item permissions for SQL database
To view permissions granted to other users have on the SQL database item, its SQL analytics endpoint, or its default semantic model, select the ellipsis (…) button next to one of these items and choose the Manage permissions quick action. Note that you need to have the Reshare permission for the item.
What you will see for the (top-level) SQL database item looks like this:
You can add and remove permissions of individual users directly in the above experience (select … next to a user).
You can also add a new user with the Add user button. This opens the Grant people access dialog. Note that you can also access this dialog via the Share quick action for your SQL database from the workspace.
The checkboxes you select under Additional permissions determine item permissions, the new user will get on the SQL database, its SQL analytics endpoint, and its default semantic model.
| Additional permission checkbox selected | SQL database | SQL analytics endpoint | Default semantic model |
| <None> | Read | Read | Read |
| Read all data using SQL database. | Read, ReadData | Read | Read |
| Read all data using SQL analytics endpoint. | Read | Read, ReadData | Read |
| Read all data using Apache Spark | Read, ReadAll | Read | Read |
| Build reports on the default dataset. | Read | Read | Build |
When to use workspace roles and item permissions with SQL database
Workspace roles provide an easy way to control access for all items in a workspace.
If you take a closer look at Figure 3, you’ll notice it shows 4 different users. Each of these users is a member of a different workspace role. You can see item permissions for a SQL database, each user gets via their membership in one of the roles:
- Admin and Member include all item permissions on SQL database items.
- Contributor includes all item permissions except Reshare.
- Viewer maps to Read and ReadData.
One important conclusion from the above is that workspace roles practically support only two distinct use cases for SQL database items:
- Provisioning administrative access to all SQL databases (as well as other items) in the workspace. You can achieve that by using Admin, Member, or Contributor. All these roles include the Write permission, which makes role members full administrators (DBAs) of all databases.
- Provisioning read-only access to all data in all SQL databases (and other items) in the workspace, which you can achieve by using the Viewer role.
Managing individual item permissions gives you a little more control. You can manage which users/apps:
- Can connect to a specific database (via Read permission).
- Can read data in a database with Transact-SQL queries (ReadData).
- Have full administrative access to the database, including the full Transact-SQL surface area (Write).
- Can access mirrored data directly from OneLake (ReadAll).
- Manage item permissions for other users in Microsoft Fabric (Share).
One great thing about workspace roles and item permissions is that they are very easy to set up, especially for members of your team who have no SQL expertise. Neither of them requires knowledge of Transact-SQL commands or even understanding the complexities of the native SQL permission system.
On the other hand, workspace roles and item permissions do not support fine-grain access control. For example, you can’t use them to permit a user to access specific tables or specific administrative functions inside the database. For such cases, you need to use SQL native features, such as SQL permissions or database roles. That, however, is a topic for a another blog post.
Next steps
Get started for free today with a Fabric free trial, if you haven’t already. If you are already using Fabric, contact your Tenant Admin to get SQL database in Fabric enabled, it does not depend on a specific capacity. To check region availability of SQL database in Fabric, refer to our documentation Microsoft Fabric supported regions .
Explore the documentation and build with our end-to-end tutorials. Specifically, for documentation on access control in SQL database, refer to Authorization in SQL database – Microsoft Fabric | Microsoft Learn.
