Configure granular access control for a SQL database

Applies to: ✅ SQL database in Microsoft Fabric

Fabric workspace roles and item permissions allow you to easily set up authorization for your database users who need full administrative access or read-only access to the database.

To configure granular database access, use SQL access controls: Database-level roles, SQL permissions, and/or row-level security (RLS).

You can manage membership of database-level roles and define custom (user-defined) roles for common data access scenarios using Fabric portal. You can configure all SQL access controls using Transact-SQL.

Manage SQL database-level roles from Fabric portal

To begin managing database-level roles for a Fabric SQL database:

1. Navigate to and open your database in Fabric portal.
2. From the main menu, select Security and select Manage SQL security.

1. The Manage SQL security page opens.

To add a new custom (user-defined) database-level role that allows its members to access objects in specific schemas of your database:

1. In the Manage SQL security page, select New.
2. In the New role page, enter a role name.
3. Select one or more schemas.
4. Select permissions you want to grant for role members for each selected schema. Select, Insert, Update, and Delete permissions apply to all tables and views in a schema. The Execute permission applies to all stored procedures and functions in a schema.
5. Select Save.

To alter the definition of a custom database-level role:

1. In the Manage SQL security page, select a custom role and select Edit.
2. Change a role name or role's permissions for your database schemas.

   Note

   The Manage SQL security page allows you to view and manage only the five schema-level permissions. If you've granted the role SELECT, INSERT, UPDATE, DELETE, or EXECUTE for an object other than a schema, or if you've granted the role other permissions via the GRANT Transact-SQL statement, the Manage SQL security page doesn't show them.

3. Select Save.

To delete a custom database-level role:

1. In the Manage SQL security page, select a role and select Delete.
2. Select Delete again, when prompted.

To view the list of role members and to add or remove role members:

1. In the Manage SQL security page, select a built-in role or a custom role, and select Manage access.
   • To add role members:
     1. In the Add people, groups or apps field, type a name and select a user, group or an app from the search results. You can repeat that to add other people, groups, or apps.
     2. Select Add.
     3. If some of the role members, you're adding, don't have the Read item permission for the database in Fabric, the Share database button is displayed. Select it to open the Grant people access dialog and select Grant to share the database. Granting shared permissions to the database will grant the Read item permission to the role members who don't have it yet. For more information about sharing a SQL database, see Share your SQL database and manage permissions.

     Important

     To connect to a database, a user or an application must have the Read item permission for the database in Fabric, independently from their membership in SQL database-level roles or SQL permissions inside the database.

   • To remove role members:
     1. Select role members, you want to remove.
     2. Select Remove.
2. Select Save to save your changes to the list of role members.

   Note

   When you add a new role member that has no user object in the database, the Fabric portal automatically creates a user object for the role member on your behalf (using CREATE USER (Transact-SQL)). The Fabric portal doesn't remove user objects from the database, when a role member is removed from a role.

Configure SQL controls with Transact-SQL

To configure access for a user or an application using Transact SQL:

1. Share the database with the user/application, or with Microsoft Entra group the user/application belongs too. Sharing the database ensures the user/application has the Read item permission for the database in Fabric, which is required to connect to the database. For more information, see Share your SQL database and manage permissions.
2. Create a user object for the user, the application, or their group in the database, using CREATE USER (Transact-SQL). For more information, see Create database users for Microsoft Entra identities.
3. Configure the desired access controls:
   1. Define custom (user-defined) database-level roles. To manage definitions of custom roles, use CREATE ROLE, ALTER ROLE, and DROP ROLE.
   2. Add the user object to custom or built-in (fixed) roles with the ADD MEMBER and DROP MEMBER options of the ALTER ROLE statement.
   3. Configure granular SQL permissions for the user object with the GRANT, REVOKE, and DENY statements.
   4. Configure row-level security (RLS) to grant/deny access to specific rows in a table to the user object.

Related content

• Authorization in SQL database
• Share your SQL database and manage permissions