I'm trying to create a role, to restrict access to a cube on Analysis services, so that a member of this role can only see records related to a specific country (from a dim_country dimension). So, for example, they'd only see the records where the dim_country name is 'England', and nothing at all for any other countries.
The issue I'm getting is that all records are being returned, regardless of what I'm putting in. This issue is the case when testing in BIDS (using Test Cube Security) and from Excel when I've added the Roles= into the cube connection definition.
I'd appreciate any suggestions.
The Role Definition I've made the following changes, and replicated it in AdventureWorks
General -> Read Definition Checked
Cubes -> Access =Read
-> Local Cube / Drillthrough Access selected
Dimension Data
On the Customer Dimension, in Country
Allowed Member set = [Customer].[Country].&[France]
Denied Member Set = [Customer].[Customer].[All Customers]
Enabled Visual Totals = Ticked.
As I said, the issue is that I'm getting all values returned, rather than those filtered by the country
This was caused by a known issue with Roles on SSAS.
The Role security Inheritance on Dimension Data doesn't appear to inherit.
I fixed the issue by going into the Cube Dimension Data dropdown and adding the Allowed Member set in there also. This resolved the issue.