Dimension Attribute Security on SSAS 2008 Not Working, but works fine on 2005

Question

I set up Dimension Data Access Security for a specific attribute hierarchy.  I.e  Specific Department Name on the Department dimension.  
I then processed the Cube and relevant dimension.   When testing using the Cube Brower for a specific user role , I see that now I have no access to any of the department attributes contained in that dimension.   On SSAS 2005 my security setups are working 100%  

Answer 1

Hi,

 

I think there is no breaking change in the dimension data security of SSAS 2008. It should works fine. You need to deploy the project to apply the change to the security permissions for the role. Also, this issue occurred to all the dimensions or just this one? And here is the document for 2008, you will find it’s the same as SSAS 2005:

http://msdn.microsoft.com/en-us/library/ms188959(SQL.90).aspx

 

Hope this helps,

Raymond

 

 

Answer 2

Thanks for replying Raymond


Replying to you ,   I have deployed the solution so that the security changes take effect.  The problem occurs on any dimension where attribute security hase been applied.

Another observation ,  When on the cube browser and filtering  on a dimension that has attribute security defined (it returns only the 'all' value' .  But if I process that dimension  the security role kicks in and looks correct (on browser) until i  click on the refresh or the reconnect button. ( IT resets and goes back to showing only the 'all' value) .  Could the problem be with the windows security .  We are using a combination of AD  and local account users )

Note using 64 Bit architecture  on windows server 2008


Thanks Darryl   

Answer 3

More information on the problem

I have also found that the roles are not working  for the performance point planning application as well, basically the roles are not working for any of my cubes at the moment.   

I do have Sql 2005 and sql 2008 installed on the same machine, but apparently this is not a problem.  Really do not know what to do with this problem

Answer 4

Hi Darry,

 

I feel puzzling too. Could you create a trace with profiler on server? Check the user account when one user queries data from the cube. Also, check the users’ role, because a user can belong to more than one role in an Analysis Services database. Permissions across multiple roles are additive. If the user belongs to a role which has access to all dimensions, then he/she will always have this permission even if you create another role for this user. And if the user is the Admin of the Analysis Services, then he/she can access to all of the cubes on this instance.

 

Hope this helps,

Raymond

Answer 5

Update  on the problem

Found that users had admin access to analysis services , since removed this because users accessing the cube  locally from Excel were not restricted by the Role security.   Now I get the same result as when testing through SQL Managment Studio.

However original problem still exists.  I have run the profiler and can't see anything wrong. 

Note .  I am testing the analysis services role security as follows

 a.  from SQL Server Management Studio 
 b.  Select Analysis Services
 c.  Right Click browse on the speocifed cube
 d.  change change user  ,  
 e  I then select a specific  "Role" only 
 f.  Select a Fact and then Dimension with the attribute secuirty

I noticed that there were some issues raised around secuirt settings being lost  and  the suggestion was to load SP1 and cumulative pack 1.   At  present  I am on the vanilla SQL2008 

Tks

    

Answer 6

Hi Darry,

 

I have checked ‘butcheck’, there are some known issues about dimension security on SSAS 2008, but all of them focus on a particular situation, for example: when MemberUniqueNameStyle is set to NamePath.

 

Anyway, please install the latest serve pack, if still not work, you can connect product group through:

https://connect.microsoft.com/SQLServer/

 

Or let me know, I will escalate the issue to product group.

 

Regards,

Raymond

Answer 7

Hi 

Have applied SP1 and still same issue.  Have now logged with Product as you suggested , lets wait and see response

Thanks for your help thus far  

Darryl

Answer 8

Darryl,

Did you ever get an answer from MS on this issue.  I am running into the same problem, no hierarchy attributes are displayed and only the "All" is visible.  It doesn't matter which dimension I apply security.

Rick

Answer 9

Darryl,

Applying Hot Fix 2710 ( KB969099 ) resolved the issue for me.