{"id":104782,"date":"2024-12-04T20:45:00","date_gmt":"2024-12-04T20:45:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=104782"},"modified":"2026-10-16T12:43:22","modified_gmt":"2026-10-16T12:43:22","slug":"granting-permissions-in-a-fabric-data-warehouse-and-lakehouse","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/data-analytics\/microsoft-fabric\/granting-permissions-in-a-fabric-data-warehouse-and-lakehouse\/","title":{"rendered":"Granting Permissions in a Fabric Data Warehouse and Lakehouse"},"content":{"rendered":"\n

We are becoming used to being a bit lazy when granting permissions to Data Warehouses and lakehouses in Fabric. We only go to the workspace level and add the user as a viewer or member. For more on this, see creating a Fabric service account.<\/a> For more on this, see the differences between the Fabric warehouse and lakehouse.<\/a><\/p>\n\n\n\n

However, this is far from a good idea.<\/p>\n\n\n\n

This practice not only gives the user access to all tables in the warehouse or lakehouse, but also gives access to all objects in the workspace. Bad idea. For more on this, see VNet data gateways for Power BI.<\/a> <\/p>\n\n\n\n

The Solution<\/h2>\n\n\n\n

The solution is on the old and good SQL security practices. A Fabric Data Warehouse has database roles in a very similar way as Azure SQL\/SQL Server<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

<\/p>\n\n\n\n

You can add to the roles users which have no access in the workspace. In this way, the user will receive access to the Data Warehouse\/Lakehouse, but not to the other objects in the workspace.<\/p>\n\n\n\n

But what user?<\/p>\n\n\n\n

Fabric works in the same tenant as Azure, with the same security environment. Every user available in Azure is also available in Fabric.<\/p>\n\n\n\n

For example, the statement below adds a user to the db_datareader role in a data warehouse<\/p>\n\n\n\n

alter role db_datareader add member [dennes2@dennesbufaloinfocom.onmicrosoft.com]<\/p>\n\n\n\n

After Adding the user<\/h2>\n\n\n\n

You can use the system table sys.database_principles<\/strong> to identify the users. An Azure user will not appear in this table, unless it received some kind of permission in the warehouse.<\/p>\n\n\n\n

For example:<\/p>\n\n\n\n

select name, principal_id, type_desc from sys.database_principals<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

<\/p>\n\n\n\n

Danger:<\/strong> It’s interesting to notice this user doesn’t appear in the option Manage Permissions in the Data Warehouse\/lakehouse. The granular permission management is good, but it can also be dangerous because you can lose track of who has access to what.<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

<\/p>\n\n\n\n

More Granular Access Permissions<\/h2>\n\n\n\n

The Database Roles available give permission to read all tables or write on all tables. What if you would like to have more granular control than this?<\/p>\n\n\n\n

You also have the DB Custom Roles. You can create a completely custom role; define the granular permissions you would like and add the user to the custom role.<\/p>\n\n\n\n

The code will be like this:<\/p>\n\n\n\n

--remove the user from the current role\n\nalter role db_datareader drop member [dennes2@dennesbufaloinfocom.onmicrosoft.com]\n\n-- create a custom role\n\ncreate role MyCustomRole\n\n-- Grant permission to the custom role\n\nGrant SELECT on dbo.customer to MyCustomRole\n\n-- add the user to the custom role\n\nalter role MyCustomRole add member [dennes2@dennesbufaloinfocom.onmicrosoft.com]<\/pre>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Checking Group Membership of a User<\/h2>\n\n\n\n
We need to be capable of checking the groups the user belongs to. This is especially important because these permissions don’t appear inside Manage Permissions.<\/p>\n\n\n\n
We can do this making some joins with system tables, but it’s not a so simple query:<\/p>\n\n\n\n
SELECT\n    p.name AS PrincipalName,\n    p.type_desc AS PrincipalType,\n    r.name AS RoleName\nFROM\n   sys.database_principals p\n   LEFT JOIN\n   sys.database_role_members rm \n        ON p.principal_id = rm.member_principal_id\n   LEFT JOIN\n   sys.database_principals r \n        ON rm.role_principal_id = r.principal_id\nORDER BY p.name;<\/pre>\n\n\n\n
The table sys.database_role_members<\/strong> needs to be joined twice with sys.database_principals, one to retrieve the name of the user who belongs to a group and the other to retrieve the name of the group.<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Lakehouse<\/h2>\n\n\n\n
All these features also work in a SQL Endpoint of a lakehouse, but with some additional details:<\/p>\n\n\n
\n
    \n
  • The SQL Endpoint in a lakehouse is already read-only<\/li>\n\n\n\n
  • There were restrictions about if spark code in a notebook would respect the rules. This evolves all the time, take care and check the current state of this.<\/li>\n<\/ul>\n<\/div>\n\n\n
    Summary<\/h2>\n\n\n\n
    We can and we should be managing permissions in our Fabric environment in a granular way. We need to leave behind old practices from Power BI era and increase the security level of our solutions<\/p>\n","protected":false},"excerpt":{"rendered":"
    We are becoming used to being a bit lazy when granting permissions to Data Warehouses and lakehouses in Fabric. We only go to the workspace level and add the user as a viewer or member. For more on this, see creating a Fabric service account. For more on this, see the differences between the Fabric……<\/p>\n","protected":false},"author":50808,"featured_media":104788,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[159164],"tags":[123645,158998,158997,4619],"coauthors":[6810],"class_list":["post-104782","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-microsoft-fabric","tag-data-warehouse","tag-lakehouse","tag-microsoft-fabric","tag-security"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104782","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=104782"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104782\/revisions"}],"predecessor-version":[{"id":113852,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104782\/revisions\/113852"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/104788"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=104782"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=104782"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=104782"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=104782"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}