Securing the Cube - Expert Cube Development with SSAS Multidimensional Models - page 246

If you have worked with security in Windows, you will probably know about the
"deny wins over grant" rule. This means that, if a user belongs to a group that grants
access to a resource and at the same time belongs to another group where access
to that resource is explicitly denied, the user will not have access to that resource
because the deny in the second group wins over the grant in the first one.

In Analysis Services, there is no such rule. When a user is a member of multiple
roles, that user will have all of the permissions granted to them in each of the
roles—the permissions of each role are combined together. For example, if a user
belongs to a role that can only see data for Canada and no other country, and
another role that can only see data for France, that user will be able to see data for
both Canada and France. Similarly, if a developer is a member of a role that gives
them full administrative permissions, this will guarantee that they can perform all
administrative tasks and see all data in all cubes, even if they are also members of
roles with more limited permissions. This might seem strange but, like it or not, this
is how Analysis Services security works.

Testing roles
If we as developers have full administrative permissions, then we need a way
to simulate membership of more restrictive roles so that we can test they work
properly. There are three ways of doing this:

• We can set the following two connection string properties when connecting

to the cube. Connection string properties can be set in SQL Management
Studio in the Additional Connection Properties tab of the connection dialog.
° Roles : This property takes a comma delimited set of role names; when
this property is set, the user will then connect to the server as if they
were a member of these roles. In order to use this option, the user
needs to be either an administrator or belong to the roles in question.
° EffectiveUserName : This property takes a Windows username in
the form DomainName\Username ; when this property is set the user
will then connect to the server impersonating this user. To use this
property, the user must be an administrator.