Testing/Troubleshooting Analysis Service Cube Security from SQL Server Management Studio

security  Analysis Service Security model is role based and relies on Windows authentication. This could pose a challenge for development and support teams to test/troubleshoot role permissions because of sensitivities around windows password. Thankfully Microsoft SQL Server Management Studio (SSMS) provides a feature to test/troubleshoot role permissions and the best thing is – you don’t need to know the password of the windows account you are testing.   

 

The SSMS cube browser presents the Change User feature (Screen Capture 1 and 2) which allows test/troubleshoot the security context of:   

 

  •  Current User
  • Other User
  • Roles
    Screen Capture 4 - By Role
    Screen Capture 1- Change User

    Screen Capture 2 - Security Context
    Screen Capture 2 - Security Context

Current User

This is the default mode for cube browsing. It uses the currently logged in windows account. This mode is useful to identify very obvious permission issues like no read permission on cube/dimension (Screen Capture 3), wrongly configured dimension data security etc.        

 

Screen Capture 3 – Cube does not have Read Permission
Screen Capture 3 – Cube does not have Read Permission

Roles

Security Context by Roles helps identify issues especially around Dimension Data Security and Cell permissions. This is often used by developers and administrators to test newly created role.    For Example Screen Capture 4 shows roles on Adventure Works cube that filters Sales Territory dimension data by country. Notice that the role Australia restricts cube browsing to only the slice that contains Australia as Sales Territory.  

 

Screen Capture 4 - By Role
Screen Capture 4 - By Role

Other User

This is the most potent among the security context options simply because it helps to determine the net effect of multiple role memberships on the windows account under test. In Analysis Service security model, the least restrictive role overrides the most restrictive role. That’s why this particular option is very useful to understand the role membership implication when the user is associated with more than one role.    As an example, let’s say Bob is country manager for Adventure Works, Canada. He is a member of Canada role. So if I want to test Bob’s role permissions it would be similar to Screen Capture 5 which shows Bob can browse Canada’s entire slice and nothing more.    

 

Screen Capture 5 – Most Restrictive Role
Screen Capture 5 – Most Restrictive Role

Now, let’s say Bob reports to Sam and Sam is the regional manager for North America. Sam is a member of each of the country roles of North America. Sam’s security context will be something similar to Screen Capture 6 where his role (least restrictive) permits him to slice data for North America including Canada (most restrictive).    

 

Screen Capture 6 - Least Restrictive Role
Screen Capture 6 - Least Restrictive Role

Change User feature of SSMS removes the guesswork in resolving permission issues that might otherwise be required.    

 

BENNY AUSTIN

 

11 thoughts on “Testing/Troubleshooting Analysis Service Cube Security from SQL Server Management Studio

  1. I have figured out the problem. The MdxScript error was generated by the Cube Script because I had a Named Set defined that no longer was valid because of security. Since the role excluded the QUE member, and the Named Set included QUE, this is where the problem was.
    Once I commented out the Named Set in the Cube Script, then the problem went away.

      1. Problem is I still need the named sets in my cube script… I cannot simply comment them out, since the named sets are being used for other purposes.

        I’ve searched quite a bit on this subject, but cannot find a solution. Do you have any other suggestions? I’ve read that setting the MDXMissingMemberMode setting on each dimension may help, but I have not been successful with that either.

      2. For the named set try using a hidden dimension which is not secured . Link the hidden dimension to your fact using the same sk which can be made available as a named calculation.

  2. No, the dimension is not role playing. The dimension is shared between cubes, but I want to set the security at the cube-level (which I have done in creating the role), since the security may be different in each cube.

  3. Thanks for the great article. However, I am having a problem. Whenever I attempt to select a role, I get an MDX error and I cannot browse the data. Any ideas?

    MdxScript(MarkA_AS_SecurityTest) (108, 36) The level ‘&[QUE]’ object was not found in the cube when the string, [RegionMA].[Default].&[QUE], was parsed.

    I know the member QUE is present in the cube, but the role definition excludes QUE (since I do not want to see it for this particular role). I’m not sure where to proceed from here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s