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 or data 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.

In Fabric, you can control access using Fabric workspace roles and item permissions.

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.

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.

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.

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.

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 MEMBER and DROP MEMBER options of the ALTER ROLE statement. To manage definitions of user-defined roles, use CREATE ROLE, ALTER ROLE, and DROP ROLE.
• 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.

• Share your SQL database and manage permissions
• Configure granular access control for a SQL database
• Permission model in Microsoft Fabric
• Protect sensitive data in SQL database with Microsoft Purview protection policies