Fabric is a platform that is taking everyone in the Microsoft data stack with speed. Data for the masses, warehouses at your fingertips, everyone a lakehouse whenever they want. Sounds appealing, but at some point you’d like to secure all that data. This article tries to delve a bit into how to manage that security.

Apart from the numerous articles that parrot the classic setup : workspace roles, OLS, RLS, etc… for a single user, I would like to describe an approach that doesn’t give you headaches on managing that access. The basic setup can be found in the documentation, but it doesn’t go into detail on how the apply this on a manageable scale.

If you want to give someone access to the entire warehouse, go ahead with the Permissions in the Fabric portal.

However, if you want to give limited access to certain objects (OLS) for certain users (groups), you’ll need some SQL statements.

First off, managing individual access to individual users is good in a experimenting phase, but you know that you’ll need a more robust setup going to Test and Production.

To not have to repeat all the access again and again, you create a custom DB role, which will define what is granted and what not.

CREATE ROLE inventory_role

Next, you can add the different GRANT types you need like you’re used to in SQLServer.
The below statement gives access to all objects in the inventory schema for the role defined above.

GRANT SELECT on schema :: inventory to inventory_role

Finally, you add members to the role. The old sp_addrolemember doesn’t exist, so you’ll need to use ALTER ROLE statement.

ALTER ROLE inventory_role ADD MEMBER [mattias.desmet@axis0.onmicrosoft.com]

When you now connect to the SQL endpoint, your user will only see the objects to which he is granted through the role. (note: don’t give that user also workspace permissions)

It’s now easy to add other users with the same permissions, by adding him/her/it to the ROLE.

Some pitfalls to avoid:

  • use [] around the user name
  • users cannot be created using CREATE USER, but are created when access is GRANTed, so don’t try to create a login, a user, etc…
  • When receiving the error Login failed for user ‘<token-identified principal>’ when connecting to the SQL end-point, make sure to specify the database name in the connection dialog.