Edit

SQL granular permissions in Microsoft Fabric

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

When the default permissions from workspace role assignment or item permissions don't give you the level of control you need, use standard SQL constructs in a Fabric warehouse or SQL analytics endpoint for finer-grained access control.

For the SQL analytics endpoint and Warehouse in Microsoft Fabric:

  • Manage object-level security with the GRANT, REVOKE, and DENY T-SQL statements.
  • Assign users to SQL roles, both custom database roles and built-in database roles.

Configure user granular permissions in a Fabric warehouse

  • For a user to connect to a Fabric warehouse or SQL analytics endpoint, you must assign them to a workspace role or grant them the item Read permission. Without Read permission at a minimum, the connection fails.
  • To set up a user's granular permissions before they connect to the warehouse, define the permissions in SQL first. Then give them access by assigning a workspace role or granting item permissions.

CREATE USER limitation

  • You can't explicitly run CREATE USER in a Fabric warehouse or SQL analytics endpoint. When you run GRANT or DENY, Fabric creates the database user automatically. The user can't connect until they also have sufficient workspace-level rights.

View my permissions

After a user connects to a Fabric warehouse or SQL analytics endpoint through the SQL connection string, they can view the permissions available to them by using the sys.fn_my_permissions function.

Database-scoped permissions for the current user:

SELECT *
FROM sys.fn_my_permissions(NULL, 'Database');

Schema-scoped permissions for the current user:

SELECT *
FROM sys.fn_my_permissions('<schema-name>', 'Schema');

Object-scoped permissions for the current user:

SELECT *
FROM sys.fn_my_permissions('<schema-name>.<object-name>', 'Object');

View permissions granted explicitly to users in a Fabric warehouse

When connected to a Fabric warehouse or SQL analytics endpoint through the SQL connection string, a user with elevated permissions can query granted permissions by using system views. This query doesn't return permissions that users receive through a Fabric workspace role or item.

SELECT DISTINCT pr.principal_id, pr.name, pr.type_desc,
    pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id;

Data protection features in a Fabric warehouse

In the Warehouse and SQL analytics endpoint, you can restrict access to specific columns and to specific rows in a table, and mask sensitive data from nonadministrators.

Related content

  • Last updated on