Fabric Warehouse permissions vs workspace permissions

[gkocev]

Hello, 

I have a workspace(WS) with Warehouse and security group(SG) with Contributor rights for the workpace. The idea is that users part of the SG can create semantic models and reports in the workspace, and create their own tables/stored procedures in the Warehouse - except in the dbo schema, which should be locked for modifications (read only). 

By adding explicit restrictions in the Warehouse to the SG for dbo schema (DENY INSERT, ALTER...etc) I am able to achieve my goal. 

The issue that I face is stop users to modify these restrictions - seems that applying

DENY ALTER ANY USER, ALTER ANY ROLE ON DATABASE::[db_name] TO [sg-xxxxx] is overwriten by the fact that SG is Contributor in the WS, so any user from the SG can revoke the applied restrictions. 

Is there any way to achieve this besides creating separate workspaces for the DWH (where SG will be Viewer and explicit rights will be applied in SQL) and semantic/reporting (where SG will be contributor)?

Thanks!
Georgi

[gkocev]

Hi @tayloramy ,

Many thanks for your response! To me it is really strange that some SQL permissions (DENY/GRANT INSERT, UPDATE, EXECUTE etc.) can be applied to restrict Contributor rights and some not. Maybe it only works on object-level permissions and doesn't for DB level permissions - haven't tested them all 🙂 But I guess there is some meaningfull reason for that and we'll have to live with it 🙂

Regards 

[tayloramy]

Hi @gkocev, 

Interesting... I actually wasn't aware that DENY permissions worked when workspace roles were granted. I will need to test this in my environment. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

[gkocev]

Hi @tayloramy ,

Interesting indeed...

This is what we tested: 

I add a user as admin or contributor(tested both ways), then I DENY his premissions to ALTER, EXECUTE, INSERT, DELETE, UPDATE for dbo schema - as result he was not able to perform any of these actions in the dbo schema, only read. He was still able to create a new schema and work there, which was the desired result. 

But when applying DENY to ALTER ANY USER, ALTER ANY ROLE in the Warehouse - this was not working, he was still able to modify other user's permissions. 

Then did the same by adding a security group as contributor, including the user in it and removing his 'direct' WS access  - same result with the difference that the user can modify the permissions for the security group and remove any DENY-als  🙂

[tayloramy · Accepted solution]

Hi @gkocev, 

You are correct that the workspace level contributor role overrides anything you try to do in SQL granular permissions.  

I don't think this is possible if you must grant contributor, maybe you can have the warehouse in a different workspace where the users only have the SQL granular permissions and no workspace roles, and they can build reports in the first workspace where they have contributor?

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.