Grant custom access to dimension data (Analysis Services)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
After enabling read access to a cube, you can set additional permissions that explicitly allow or deny access to dimension members (including measures contained in the Measures Dimension containing all of the measures used in a cube). For example, given multiple categories of resellers, you might want to set permissions to exclude data for a specific business type. The following illustration is a before-and-after effect of denying access to the Warehouse business type in the Reseller dimension.
By default, if you can read data from an Analysis Services cube, you automatically have read permissions on all measures and dimension members associated with that cube. While this behavior might be sufficient for many scenarios, sometimes security requirements call for a more segmented authorization strategy, with varying levels of access for different users, on the same dimension.
You can restrict access by choosing which members to allow (AllowedSet) or deny (DeniedSet) access. You do this by either selecting or deselecting dimension members to include or exclude from the role.
Basic dimension security is the easiest; you simply select which dimension attributes and attribute hierarchies to include or exclude in the role. Advanced security is more complex and requires expertise in MDX scripting. Both approaches are described below.
Not all measures or dimension members can be used in custom access scenarios. A connection will fail if a role restricts access to a default measure or member, or restricts access to measures that are part of measure expressions.
Check for obstructions to dimension security: default measures, default members, and measures used in measure expressions
In SQL Server Management Studio, right-click a cube and select Script Cube as | ALTER To | New Query Editor Window.
Search for DefaultMeasure. You should find one for the cube, and one for each perspective. When defining dimension security, avoid restricting access to default measures.
Next, search for MeasureExpression. A measure expression is a measure, based on a calculation, where the calculation often includes other measures. Verify that the measure you want to restrict is not used in an expression. Alternatively, go ahead and restrict access, just make sure to also exclude all references to that measure throughout the cube.
Finally, search for DefaultMember. Make a note of any attributes that serve as a default member of an attribute. Avoid putting restrictions on those attributes when setting up dimension security.
In SQL Server Management Studio, connect to the instance of Analysis Services, expand Roles for the appropriate database in Object Explorer, and then click a database role (or create a new database role).
The role should already have read access to the cube. See Grant cube or model permissions (Analysis Services) if you need help with this step.
On Dimension Data | Basic, select the dimension for which you are setting permissions.
Choose the attribute hierarchy. Not all attributes will be available. Only those attributes having AttributeHierarchyEnabled appear in the Attribute Hierarchy list.
Choose which members to allow or deny access. Allowing access, through the Select all members option, is the default. We suggest you keep this default and then clear individual members that should not be visible to the Windows user and group accounts in the Memberships pane via this role. The advantage is that new members added in future processing operations are automatically available to people connecting through this role.
Alternatively, you can Deselect all members to revoke access overall, and then pick which members to allow. In future processing operations, new members are not visible until you manually edit dimension data security to allow access to them.
Optionally, click Advanced to enable Visual Totals for this attribute hierarchy. This option recalculates aggregations based on the members available through the role.
When applying permissions that trim dimension members, aggregated totals are not recalculated automatically. Suppose the All member of an attribute hierarchy returns a count of 200 before permissions are applied. After applying permissions that deny access to some members, All still returns 200, even though the member values visible to the user are much less. To avoid confusing the consumers of your cube, you can configure the All member be the aggregate of just those members to which role members, rather than the aggregate of all of the members of the attribute hierarchy. To invoke this behavior, you can enable Visual Totals on the Advanced tab when configuring dimension security. Once enabled, the aggregate is calculated at query time rather than retrieved from pre-calculated aggregations. This can have a noticeable effect on query performance, so use it only when necessary.
In Grant custom access to cell data (Analysis Services), it was explained that fully hiding all visual aspects of a measure, and not just its cell data, requires permissions on dimension members. This section explains how to deny access to the object metadata of a measure.
On Dimension Data | Basic, scroll down the Dimension list until you reach cube dimensions, and then select Measures Dimension.
From the list of measures, clear the check box for measures that should not appear to users connecting via this role.
Check the Prerequisites to learn how to identify measures that can break role security.
If you have MDX expertise, another approach is to write MDX expressions that set the criteria for which members are allowed or denied access. Click Create Role | Dimension Data | Advanced to provide the script.
You can the MDX Builder to write the MDX statement. See MDX Builder (Analysis Services - Multidimensional Data) for details. The Advanced tab has the following options: