I am trying to implement security on a shared dimension (location) as well as
cube security using a combination of database roles. I've set up one group
of roles that grants access to cubes and another set of roles that grants
access to the appropriate members of the location dimension.

I have set the database roles that grant cube access to have access to the
location dimension fully restricted. I have set the database roles that
grant access to the appropriate members of the location dimension to not have
access to any cubes.

Each user then has two database roles - one to determine which cube(s) they
can access and the other to determine which location(s) are visible.

When I test this out by browsing a cube, the location dimension appears to
be fully restricted. It seems like the role granting access to the cube is
controlling what members of the location dimension are visible in that cube,
rather than the role that is set up just to control the location dimension
security (but has access to no cubes.)

If I give the user just one role (combining cube and dimension security) it
seems to work as I expect.

Any ideas as to what might be wrong or how I can better set this up?

Thanks!

The cube check boxes in a DB role don't behave quite the way you assume.
Unchecking a box for a cube switches off the role for that cube. So, if
DBRoleA does not have a check next to Cube3, then this role will not be
considered when someone attempts to access Cube3.

I'm not sure how clearly I explained that. Let me know if I need to try
again.
--
Dean Adam
Magenic Technologies


"Mike" wrote:

Dean,

Thanks for responding. I think I understand what you're saying. I do see
that if I don't check the box for the cube in the database role,
the dimension security rule I have in that database role is not applied to
that cube.

I guess this means that I cannot have separate roles that only handle
dimension security for my locations without granting access to specific
cubes. I'll have to create a role for each location/cube combination that I
need. I had hoped to avoid that because with 20 locations and 10 cubes, I'll
have a lot of roles to create.

Does that sound right?

Thanks again,
Mike

"Dean Adam" wrote:

Mike,

That's right.

With that number of roles, I would give some thought to using dynamic
dimension security. If you can handle the location security dynamically,
e.g. based on a list of users in a member property in the location dim, then
you would be able to have just one role per cube.

--
Dean Adam
Magenic Technologies


"Mike" wrote:

Dynamic security
http://www.mosha.com/msolap/samples/...20security.zip
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Dean Adam" <DeanAdam (AT) discussions (DOT) microsoft.com> wrote

Dave,

Is the recommended method to accomplish dynamic security in AS2005 the same?
If not do you have any links that describe a best practice scenario for this
in AS 2005?

Thanks,

John

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote