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
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.
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.
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.
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).
Change User feature of SSMS removes the guesswork in resolving permission issues that might otherwise be required.
11 thoughts on “Testing/Troubleshooting Analysis Service Cube Security from SQL Server Management Studio”
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.
Mark, Thanks for letting me know. Glad you figured that out.
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.
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.
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.
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.
Is you dimension Role Playing ? Because role playing dimensions have a different name in the context of cube and database. Role playing dimensions need to be secured at cube level instead of database level – https://bennyaustin.wordpress.com/2009/12/29/securing-role-playing-dimensions-in-analysis-services/
Nice troubleshooting tips! Thank you for sharing.
related post on cube security – http://wp.me/pxNuz-6a