Authorization in SQL database in Microsoft Fabric
Applies to: ✅ SQL database in Microsoft Fabric
This article explains access control for SQL database items in Fabric.
You can configure access for your SQL database at two levels:
- In Fabric, by using Fabric access controls - workspace roles and item permissions.
- Inside your database, by using SQL access controls, such SQL permissions or database-level roles.
The access controls at these two different levels work together.
- To connect to a database, a user must have at least the Read permission in Fabric for the Fabric database item.
- You can grant access to specific capabilities in using Fabric access controls, SQL access controls, or both. A permission to connect to the database can only be granted with Fabric roles or permissions.
- Denying access (with the DENY Transact-SQL statement) in the database always takes priority.
Fabric access controls
In Fabric, you can control access using Fabric workspace roles and item permissions.
Workspace roles
Fabric workspace roles let you manage who can do what in a Microsoft Fabric workspace.
- For an overview of workspace roles, see Roles in workspaces.
- For instructions on assigning workspace roles, see Give users access to workspaces.
The following table captures SQL database-specific capabilities, members of particular workspace roles are allowed to access.
| Capability | Admin role | Member role | Contributor role | Viewer role |
|---|---|---|---|---|
| Full administrative access and full data access | Yes | Yes | Yes | No |
| Read data and metadata | Yes | Yes | Yes | Yes |
| Connect to the database | Yes | Yes | Yes | Yes |
Item permissions
Fabric Item permissions control access to individual Fabric items within a workspace. Different Fabric items have different permissions. The following table lists item permissions that are applicable to SQL database items.
| Permission | Capability |
|---|---|
| Read | Connect to the database |
| ReadData | Read data and metadata |
| ReadAll | Read mirrored data directly from OneLake files |
| Share | Share item and manage Fabric item permissions |
| Write | Full administrative access and full data access |
The easiest way to grant item permissions is by adding a user, an application, or a group to a workspace role. Membership in each role implies the role members have a subset of permissions to all databases in the workspace, as specified in the following table.
| Role | Read | ReadAll | ReadData | Write | Share |
|---|---|---|---|---|---|
| Admin | Yes | Yes | Yes | Yes | Yes |
| Member | Yes | Yes | Yes | Yes | Yes |
| Contributor | Yes | Yes | Yes | Yes | No |
| Viewer | Yes | Yes | Yes | No | No |
Share item permissions
You can also grant Read, ReadAll, and ReadData permissions for an individual database by sharing the database item via the Share quick action in Fabric portal. You can view and manage permissions granted for a database item via the Manage permissions quick action in Fabric portal. For more information, see Share your SQL database and manage permissions.
SQL access controls
The following SQL concepts allow much more granular access control in comparison to Fabric workspace roles and item permissions.
- Database-level roles. There are two types of database-level roles: fixed database roles that are predefined in the database, and user-defined database roles that you can create.
- You can manage membership of database-level roles and define user-defined roles for common scenarios in Fabric portal.
- For more information, see Manage SQL database-level roles from Fabric portal.
- You can also manage role membership and role definitions using Transact-SQL.
- To add and remove users to a database role, use the
ADD MEMBERandDROP MEMBERoptions of the ALTER ROLE statement. To manage definitions of user-defined roles, use CREATE ROLE, ALTER ROLE, and DROP ROLE.
- To add and remove users to a database role, use the
- You can manage membership of database-level roles and define user-defined roles for common scenarios in Fabric portal.
- SQL permissions. You can manage permissions for database users or database roles by using the GRANT, REVOKE, and DENY Transact-SQL statements.
- Row-level security (RLS) allows you to control access to specific rows in a table.
For more information, see Configure granular access control for a SQL database.