RE: Dimension security not respected in Excel

#1

Did you try the same query and login with MDX sample application? If so,
what's the result.
Make sure there are no other roles defined within the same cube which may
overwrite the restrictions.

Thanks

Wei Zhang
Microsoft OLAP PSS
This posting is provided "AS IS" with no warranties, and confers no rights.

#2

Have you tested the role using the test button in the Analysis Mgr UI?

FYI a client app is not capable of overriding security.

tom @ the domain below
www.tomchester.net

"Wario78" <member43429 (AT) dbforums (DOT) com> wrote

Quote:

Hello,

I know this issue has been dealt with a number of times previously but
none of the suggested solutions have worked for me.

I am trying to restrict access to a 'User' dimension based on the domain
login of a given user, so that the user can only see fields in the cube
relevant to them. I cannot get it to work even in the most simple case
of defining a single database role for a single domain user which is
restricted to a single member in the dimension. In other words:-

Enforce on: Server

Membership: DOMAIN\jbloggs

Cubes: only one cube

Restricted Dimensions: User

Advanced Dimension Security on User:

Top Level: [User].[User]

Bottom Level: [User].[User]

Allowed Members: {[User].[User].&[Joe Bloggs]}

Denied Members: None

Testing this role produces the desired results (only show data for Joe
Bloggs) but when viewing the cube in Excel using that DOMAIN\jbloggs
data for all users is accessible.

I have tried using an MDX expression to filter the results dynamically
according to a 'NTUser' property I have on User but still with no
success. Incidentally, I added a calculated member which returns the
'UserName' function. When testing the role, this correctly returns my
domain\login but in Excel it just returns '0'. Despite this, setting
HKLM\SOFTWARE\Microsoft\OLAP Server\CurrentVersion\AuditEvents to 0xf
(in order to log connect/disconnect events in EventLog) shows that Excel
is definitely connecting using DOMAIN\jbloggs.

I'm using Excel 2K, AS2KSP3a, SQL2KSP3.

I've tried all the suggestions I can find and nothing works. Does anyone
know how I can get Excel to pay attention to the restricted dimension?
Any help is very much appreciated.

Regards,

Gavin

--
Posted via http://dbforums.com

#3

I found the problem. The domain login that I was trying to restrict
access for was a member of the 'Domain Admins' group and therefore had
administrator rights on the Analysis Server machine. However, 'Domain
Admins' was not a member of the 'OLAP Administrators' group the domain
login still got access to all data. Removing 'Domain Admins' from the
Administrators group on the Analysis Server solved the problem.

Thanks for your suggestions,

Gavin

--
Posted via http://dbforums.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

RE: Dimension security not respected in Excel

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

RE: Dimension security not respected in Excel - 10-13-2003 , 04:33 PM

Re: Dimension security not respected in Excel - 10-14-2003 , 08:00 PM

Re: Dimension security not respected in Excel - 10-27-2003 , 12:01 PM