GDavids - SAO

Hi everyone

I have searched the forums for a solution, but i cannot find one that i can see apllies
thus I post.

I am having trouble restricting the access of one of my roles.
Here is the structure of my cube:

Cube: 'Sales'
Dimension : 'Product'
Hierarchy: 'Code - Company'
Member: '123'

I simply need to restrict the user to this member. I have set up the
security in Dimesion data on : Cube>>Dimension and Dimension...
I only selected
[Product].[Code - Company].&[123]
but when viewing a report using this user it doesn't seem to take effect,.
{All the company codes can still be seen in the company code parameter drop down}

When I try to implement this on the cell data tab, I can't see anything in the dropdown
when running the report in the browser

{Using 'Enable read permissions', 'Enable read
contingent Permissions' or a combonation of both}

I'm stuck as can be! Please help to shed some light on
this for me.

I thank you in advance

Gerhard Davids
(PS: If I have been unclear in anyway point it out to me plz
and I shall rephrase it)

Re: Problem with role-based security

Dave Fackler

Do you have any other roles that the user is a member of that would provide them access to the other company codes Roles in AS2005 are cumulative, so if a user has access to a set of dimension members via one role but they are restricted to a subset of the dimension members via another role, they will still be able to see all of the dimension members...


Dave Fackler

Re: Problem with role-based security

Glaciered Pyro

Hi Dave

Thanks for the response.

No, th user is only part of one role so he cannot be overiden by
another. The user is also not part of the Domain, I dont know if that changes anything.
The user is only added on the local-mashine fo the reporting services.

Cube security work when I use the test cube security via the link in
the cell data tab.


Re: Problem with role-based security

Mosha Pasumansky

Two things to check:

1. Run Profiler for AS and make sure that the connection open from RS indeed authenticates as this user

2. Check whether this user is member of Administrators NT group on the machine - if so security doesn't apply to him by default

3. Check response from DISCOVER_CATALOGS while connected as this user and see the content of ROLES column. If it has * in it - it is a bad sign.

Re: Problem with role-based security

Glaciered Pyro

Hi Mosha

Thank you for the responce.

I ran the profiler(first time using profiler..) and connected to my ssas
then connected to the reports server and opened the report etc...

The stack trace that was produced did show the correct user authenticated,
The user is also not part of the NT Admin group.

The trace did not show a DISCOVER_CATALOGS and no roles
column was present, however other DISCOVER_ were shown.
Did I do something incorect for this data to be missing

The only strange thing that i saw was upon clicking the drop down
for the parameter the MDX querry to populate, the 'querry end' event's
error column contained a '1'.


Note: I am using SSAS 2005

Re: Problem with role-based security

Glaciered Pyro

Anyone have any ideas as to what may be causing this

Re: Problem with role-based security

Deepak Puri

Hi Gerhard,

Are you using separate Analysis and Reporting servers If so, the Report server doesn't connect to SSAS using the ID of the report end-user, unless you're using Kerberos - see this past post in the SQL Server OLAP newsgroup:


microsoft.public.sqlserver.olap > AS + RS


Sounds like the classic NT 2-hop authentication problem.
NT credentials can only be passed between two machines (i.e. client and then
RS server). If you attempt to transfer them again from the RS server to the
AS server, then you get a blank username (actually an error, depending on
the OS and its settings). This is a well-known limitation of NT -- it is
totally unrelated to RS or AS. If you really need to do this then you have a
few choices:
1) run RS and AS on the same machine
2) implement kerberos
You could also switch to saved connections on the RS machine, but that would
defeat the dynamic security that you have already established.
Dave Wickert [MSFT]
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)