By: Siddharth Mehta
Overview
Now that the users are configured for the SQL Server Analysis Services (SSAS) database role, it's time to setup the role's access on individual SSAS database objects. In this chapter we will look at the different options for the database role to setup data level security.
Explanation
Data Sources - SSAS reads data from numerous external data sources through a mechanism called Data Sources. Here we have the option to configure whether to provide access to these Data Sources for the role. By default this would be set to None, but you can provide Read permissions on the Data Source if required.
Cubes - This tab provides options to configure access on different cubes. There are three different settings available to configure for each cube - Access, Local Cube / Drill through Access and Process.
- Access - This option indicates whether the users have Read, Read and Write or no access to the cube. Write here means the SSAS write-back feature, and should not be confused with data modification / manipulation as is the case with SQL Server tables.
- Local Cube / Drill through Access - This option indicates whether the users have access to perform drill-through on the cube data from the client application.
- Process - This option indicates whether the users have privileges to perform the process operation on the cube.
Cell Data - This tab provides the options to configure cell level data security. In SSAS terminology, a cell is the most basic unit of data also called a tuple. In relational terms, it can be thought of as a field value for any given row. Here we can configure read, read-contingent and read-write permissions for the data. To configure this a SSAS administrator needs to have knowledge of two things - MDX and Actual Data.
Generally development teams are more aware of the actual data and the authorization required. Also they are much more well versed with MDX, and as a standard practice they can configure this security directly from Business Intelligence Development Studio (BIDS) / SQL Server Data Tools (SSDT). When they deploy / process the model, the security setting will be overridden and updated with the new settings. So development teams are generally well positioned to configure this security. But in production environments, administrators can fine-tune this security if required by modifying the MDX for each section.
Dimensions and Dimension Data - These two tabs facilitate fine-tuning the access to dimensions and the data within each dimension. In the dimension data tab, one can also fine tune the level of access programmatically by providing MDX script expressions.
One point to keep in mind is that although setting up security in dimension members may hide members based on user authorization, but they would continue to see the same total numbers. For example, in the below screenshot for the department dimension, there are total 6 dimensions. If we remove three of them, the removed dimensions would not be visible to the users, but if they use this dimension in a pivot table, they would see the total as 6 in spite of only 3 being visible to them. So to mitigate this problem, on the Advanced tab in the Dimension Data tab, check the "Enable Visual Totals" option.
Additional Information
- Cell security is a complex subject. Consider further reading from here.
