Our goal is to use the current UserName to restrict
access to our Stores dimension.

User1 can access StoreA, User2 can access StoreB, etc.
These permissions are maintained by a web application and
stored in a separate cube, Permissions.

The Permissions cube has a Users and Stores dimensions
with a single measure, "IsAllowed" that contains a
value "1" if the user is authorized to view the store
data.

We're trying to use the Cube Role Manager in the Stores
cube to identify the set of Stores a given UserName is
allowed to access.

Apparently, you can type an MDX snippet into the Cube
Role Manager, Restricted Dimensions, Stores dimension
Custom Settings, Advanced tab, Allowed Members field that
returns the set of Stores a UserName is allowed to access.

Logically this might read:

Filter (Store.members ,
LookupCube ( "Permissions" , "select IsAllowed from
Permissions.Stores where " + Store.CurrentMember + "
and " + StrToMember(UserName ) )
= 1
)

What would the desired MDX actually look like?

LookupCube may not work - try a virtual cube approach:


http://groups.google.com/groups?hl=e...3ZKjp3jBEHA.23
60%40TK2MSFTNGP10.phx.gbl
Subject: Re: Dynamic User Security
View: Complete Thread (5 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2004-03-09 17:22:33 PST


Indeed, LookupCube() can't be used for dimension security:

http://groups.google.com/groups?hl=e...8&th=de8e155f1
9b4cb3e&rnum=3
Subject: mdx in Cube Role Manager

View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-03-13 06:49:55 PST

Hi all!
I'm trying to create a flexible cube role by typing the following mdx in
the role manager:
Filter([Geography].[Cities].members,(LookupCube("Security","([Users].[Al
l
User Name].[" + username + "],[Measures].[USaccess])=1")))
As you guess I have a cube which stores the security information per
city.
In mdx sample application this expression works, but role manager
displays
the err. message "...Cannot evaluate the expression ....".
Maybe Cube Role Manager has some limitations for mdx expressions?

Thank you

Message 2 in thread
From: SQL Server Development Team [MSFT] (sqldev (AT) microsoft (DOT) com)
Subject: Re: mdx in Cube Role Manager

View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-03-13 09:47:59 PST

Unfortunately, the LookupCube MDX function is not supported for such an
expression - the expression will work in client applications, but not
within
the Cube Role Manager.

Hope this helps,
Dennis Kennedy
..

However, based on your MDX, you should be able to combine the
"Projektstatus" and "Projektsecurity" cubes into a secure Virtual Cube,
with [Measures].[Access] available. Then, you can apply a similar filter
condition. This [MS] webcast describes the Virtual Cube solution:

http://support.microsoft.com/default...b;EN-US;828343
Analysis Services
View products that this article applies to.
Session Summary

Wednesday, September 3, 2003

This Support WebCast session discusses how to implement dynamic
Dimension Security in Microsoft SQL Server 2000 Analysis Services. It
reviews some popular approaches to use during implementation. It also
talks about common issues that may occur and how to resolve them.
..

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!