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

  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 1- Change User

  

  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

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

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

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

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

 

BENNY AUSTIN