SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSAS Understanding Multiple Security Roles

In a previous article I showed how you can create dynamic security in SSAS.  This is a great way to make all security table based using Active Directory accounts associated with the items that the user is allowed to see. 

If you decide that your security model is pretty basic and it is not necessary to implement the table based security then you should be aware of a side effect of having multiple Analysis Services roles.  The side effect I’m referring is having a user that falls under multiple roles.  This is probably a very common occurrence when using Active Directory groups in your roles.  Let’s say for example you Bill is part of an AD group that is meant for the southeast sales department, but he also is a part of an AD group that is meant for the northwest sales department.  If these two groups are used in two different roles then Bill is able to see everything available in both roles.  When a user is part of two roles SSAS takes a UNION of those two roles.  The image below demonstrates this.  The area in green representing what Bill can see.


I hope this helps if you are trying to figure out why your security model is not working.  You know now to check and make sure a user is not part of multiple roles.

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


No comments.

Leave a Comment

Please register or log in to leave a comment.