locked
Admin vs Non Admin Empty Cell Security RRS feed

  • Question

  • Greetings!  I have a cube of financial data.  I've permissioned access to the cube with a single role, which has read access to the cube (with drill through permissions), measures, dimensions and cell data.  The role contains a single domain security group and an additional non-managerial user, who also needs access to the data.  Of the members of this role, three individuals are also members of the built in administrators group on the server.  The three administrators have, what I would consider the correct view of the data, when either browsing the cube in SSMS or using Excel as the client tool to browse.

    The problem is that the non administrators have a large number of empty cells that are returned to the client application, either browsing the cube with SSMS or with Excel.  I have browsed the cube using the permissions of each user, who has explicit or inferred through membership of the security group read permission to the cube.  I have also added an additional user to the built in administrators group and confirmed that the view of the data changed for that user.

    Here is the admin's view of the data:

    And here is the non admin's view of the data:

    I don't understand, what options for security would result in all empty cells being displayed for non admin users, and hidden for admin users.  The actual numbers between the two is the same and valid, but I would prefer to have the admin view, so that the end users aren't confused with all the "NULLs," but I don't want to add everyone to that built in administrators group.

    Hopefully I haven't rambled too much, and you can understand, what I my desired outcome would be.  Any help in understanding, what is causing this view, and how to resolve would be greatly appreciated.

    Thanks,

    Chris

    Wednesday, June 22, 2016 9:01 PM

Answers

  • So there are 2 problems here. One is that your read permissions expression is not quite right. The expression should be in the form of a statement that returns true or false. By using [Measures] you are pretty much saying that people can see all the cells that have a value, but can't see cells that don't have a value. But it sounds like you don't actually require cell level security so switching it off is the best course of action (there is a big performance overhead to applying cell level security)

    The second problem is that the cube browser in SSMS / SSDT does not show restricted cells properly. Those (null) values that you see are actually #N/A as they are secured from the current role. If a given cells is secured it will always show #N/A regardless of whether it has data or not.


    http://darren.gosbell.com - please mark correct answers

    Thursday, June 23, 2016 12:23 AM

All replies

  • So I made one small change to the role's permissions, and achieved my desired results, but I would love to have an explanation because I don't fully understand, what is going on.

    I unchecked the "Enable Read Permissions" under the Cell Data tab, and the empty cells are no longer returned.

    Wednesday, June 22, 2016 10:44 PM
  • So there are 2 problems here. One is that your read permissions expression is not quite right. The expression should be in the form of a statement that returns true or false. By using [Measures] you are pretty much saying that people can see all the cells that have a value, but can't see cells that don't have a value. But it sounds like you don't actually require cell level security so switching it off is the best course of action (there is a big performance overhead to applying cell level security)

    The second problem is that the cube browser in SSMS / SSDT does not show restricted cells properly. Those (null) values that you see are actually #N/A as they are secured from the current role. If a given cells is secured it will always show #N/A regardless of whether it has data or not.


    http://darren.gosbell.com - please mark correct answers

    Thursday, June 23, 2016 12:23 AM
  • Thank you for the explanation.  That makes sense to me, and I totally forgot about the cell data security.  I've used that once before, when we were securing data at that level.
    Thursday, June 23, 2016 4:26 PM