none
Multiple Roles for same user SSAS Multidimensional Cube

    Question

  • Hi 

    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

     


    Monday, March 20, 2017 1:08 PM

All replies

  • 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 )

    Monday, March 20, 2017 1:55 PM
  • 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.
    Monday, March 20, 2017 2:22 PM
  • Hi,

    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?

    Kr 

    Monday, March 20, 2017 2:52 PM
  • Hi Zaiem,

    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.

    Best Regards
    Willson Yuan
    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
    Tuesday, March 21, 2017 2:35 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>

    “no_bi_USA,no_bi_Canada”<o:p></o:p>

    Best Regards<o:p></o:p>

    Zaiem <o:p></o:p>


    Tuesday, March 28, 2017 9:41 AM
  •  How does the MDX in the roles(no_bi_USA,no_bi_Canada) look like? Can u make an screenshot?
    Tuesday, March 28, 2017 9:47 AM
  • 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.  

    Tuesday, March 28, 2017 10:25 AM
  • 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.

    Tuesday, March 28, 2017 11:14 AM
  • Thank you for your answer, 

    The User can se the other countries in the dimension, but only see the values for the one country he/she has access to. 

    What version of SSAS/SQL Server are you using ?

    Can you paste the setup you used so i can compare it. 

    Tuesday, March 28, 2017 11:33 AM
  • So the user uses the dimension on the rows (without zero supression (without "NON EMPTY")) and see values for Country 1 but NULL for Country 2 - right?
    Tuesday, March 28, 2017 12:50 PM
  • Yes that is right. 

    Thursday, March 30, 2017 12:46 PM
  • Hi Zaiem,

    Thanks for your response.

    In this scenario, could you please try to remove Default member in the Roles (no_bi_USA,no_bi_Canada), as the default member for any aggregatable attribute is the “All” member.

    Because a default member performs its filter behind the scenes, it can risk reducing usability rather than increasing it.  In most cases, leaving the All member is the best choice.  

    For more information, please refer to Specifying the Default Member.

    Best Regards
    Willson Yuan
    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

    Friday, March 31, 2017 8:42 AM