Edit

SQL granular permissions in Microsoft Fabric

  • Article

Applies to: SQL analytics endpoint and Warehouse in Microsoft Fabric

When the default permissions provided by assignment to workspace roles or granted through item permissions are insufficient, standard SQL constructs are available for more granular control.

For SQL analytics endpoint and Warehouse:

  • Object-level-security can be managed using GRANT, REVOKE, and DENY T-SQL syntax.
  • Users can be assigned to SQL roles, both custom and built-in database roles.

User granular permissions

  • In order for a user to connect to the database, the user must be assigned to a Workspace role or assigned the item Read permission. Without Read permission at a minimum, the connection fails.
  • If you'd like to set up a user's granular permissions before allowing them to connect to the warehouse, permissions can first be set up within SQL. Then, they can be given access by assigning them to a Workspace role or granting item permissions.

Limitations

  • CREATE USER cannot be explicitly executed currently. When GRANT or DENY is executed, the user is created automatically. The user will not be able to connect until sufficient workspace level rights are given.

View my permissions

When a user connects to the SQL connection string, they can view the permissions available to them using the sys.fn_my_permissions function.

User's database scoped permissions:

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

User's schema scoped permissions:

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

User's object-scoped permissions:

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

View permissions granted explicitly to users

When connected via the SQL connection string, a user with elevated permissions can query granted permissions by using system views. This doesn't show the users or user permissions that are given to users by being assigned to workspace roles or assigned item permissions.

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

You can secure column filters and predicate-based row filters on tables in Warehouse or SQL analytics endpoint to roles and users in Microsoft Fabric. You can also mask sensitive data from non-admins using dynamic data masking.

Related content