I am experiencing some problems regarding security and roles with my Cube. I have 10 different roles, each of them give the users access to a specific country. We get the user groups from Active Directory and everything works as it should if the users have access to all the countries or just one. The problem appears when the user has access to two or more but not all countries.
For example User A: Has Access to Canada and USA, by being in two different Active Directory groups.
As things are today, this user only has access to Canada and it seems like it is because C comes before U. Has anyone experienced this problem before? Any fixes for this problem ?
I am using SQL Server 2016
In a test or development system, try adding the AD User's Account rather than an AD Group and see if that works. I have done this in a Cube from SQL Server 2014 and it works with multiple Roles, but I have never used an AD Group.
If you test this and it works, you know the problem might be with using groups. If it still does not work, then it is probably a issue with SQL Server 2016.
Thomas LeBlanc twitter ( @TheSmilingDBA )
I have tried to put the AD user account directly in the user roles as well. Everything seems fine when i try to browse the cube as the user in Visual studio, but when the user tries to connect to SSRS or Excel he/she can only see the first role they have access to.
Have u used Profiler to see which roles are used by the User? look at the "Session initialized" event, then u will see which roles are used.
Second how does the MDX in the roles look like? Can u make an screenshot?
- Proposed as answer by willson yuanMicrosoft contingent staff Tuesday, March 21, 2017 2:35 AM
Thanks for your question.
Gernerally, if user belong to multiple roles with same dimension, SSAS will union all the value, then he/she will see everything which are combined with these roles.
If one role allows access to a member and one denies, the user will be allowed to access.
In your scenario, user A should be able to access to Canada and USA. However,Roles can be selected using Roles-parameter in the connection sting "...;Roles=Role1,Role2;...".
As Yger said, you may need to use SQL Server Profiler to see which roles are used by user A.
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
- Proposed as answer by alexander fun Tuesday, March 28, 2017 9:49 AM
Hi Everybody, <o:p></o:p>
Thanks for the help so far. <o:p></o:p>
I have now run SQL Profiler, when the session initializes the following roles appear, and everything seams like it should be. Or should I look somewhere else.<o:p></o:p>
This is how the Canada role is setup in Visual Studio, the USA role is similar. As said earlier, any user who only needs to get a single country, has no problems, but when the user needs to access two or more countries, the user only gets access to the first alfabetical, in this example Canada.
So, if the user is browsing the dimension, he see only 1 country?
Or the user just see values for 1 country without using the dimension somewhere?
Why I'm asking: u are using Visual Totals AND u are using a Default member.
I rebuild your configuration on AdventureWorks and everything works as expected.