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.