SQL Server Analysis Services has a wide range of advanced security opportunities. You can explore these opportunities through the SQL Server Management Studio. One such feature is the ability to filter the data that a role has access to by restricting access to specific members of a dimension.
You can restrict access for the IT Analytics Users role to return the cube data only for computers with a Win32 system type. For this example, you must grant access to the Computer cube for the IT Analytics Users role.
To filter a role-based cube
In SQL Server Management Studio, in the IT Analytics analysis services database, navigate to the properties for the IT Analytics Users role.
In the Edit Role dialog box, navigate to the Dimension Data page.
In the Dimension drop-down list, click the Computer dimension.
Select the Deselect all members radio symbol.
In the Attribute Hierarchy drop-down list, click Computer - System Type.
Select the dimension members that you want the role to have access to.
In our example, there is a Win32 member. Actual names are specific to each instance of Notification Server.
Navigate to the Advanced tab of the Dimension Data page.
Click Enable Visual Totals.
This step prevents the role from seeing the aggregate totals that are independent of the configured filtering and restricts aggregations.
Click OK to save the role configuration.
Users in the configured role now see the results only for the computers that have a Win32 system type across all cubes. This filtering is enforced across all means of accessing the cubes including dashboards, cubes, reports, and third-party applications.