SSAS Security Access Restriction

0
votes

I have created a cube including user roles to restrict the access. Checking with Server Data Tools, everything works fine: the user do see limited data of a particular dimension. I added an user from Active Directoy to that role. After deploying the cube including the new created role and connecting with Excel to that cube with Single Sign On (SSO), I do see all data without any limitation.

What is missing?

Regards

Matt

1
securityssascube
When you checked inside SQL Server Data Tools how did you do that? Did you click the user button in the cube browser and enter that user's DOMAIN\Username? Did you choose from the list of role names? - GregGalloway
Where is the membership of the role defined? In SQL Server Data Tools or in Management Studio? I'm wondering if you deployed over a change you made in Management Studio. - GregGalloway
Hi Greg, indeed, I checked in the cube browser the permission and switched to the defined role. Everything works fine. I added myself to the role using DOMAIN\USERNAME. Then I deployed the project so I can see the new defined role alos in Management Studio. But when I connected to the cube with Excel, I did not see any change. I was able to see all items in the cube without any restrictions. - MBR
Hi @MBR. Any update on resolving this? Did my answer help? - GregGalloway
@mbr sounds like the user is ssas admin - Pirvu Georgian

1 Answers

1
votes

Two possibilities. Adding your own username (who is an SSAS admin I assume) to a role will not lower your permissions. You will still be a SSAS admin and can this see everything. If you want to limit your own permissions in Excel you must edit the connection string in Excel and add Roles=YourRoleName

If you are trying to restrict someone else's permissions make sure that other person:

  1. Doesn't have SSAS admin permissions
  2. Isn't a Windows admin because by default there is a BuiltInAdminsAreServerAdmins=True property in SSAS which would make Windows admins be SSAS admins.
  3. Make sure that user isn't also listed as a member in another role. Permissions across roles are additive in SSAS.