The user-access privilege is necessary to set up the production environment and make sure data access is not compromised. Once the design and implementation of a cube are finalized, the build and deployment are the next step. This is where we need to ensure that the cube will be processed without any errors, so testing is essential.
The opportunity to generate cube data access is determined by the settings in the cube role. Once the database role is defined with the privileges, the users and groups members of that role will have privileges in the Analysis Services objects associated with them.
In a SQL Server Analysis Services instance, the database role has no permissions to view any cubes (data) in the database; using the right method in granting user-access within a database role will enable the read or read/write permissions to a cube. In this recipe we will look into the user-access privileges on the cube.
Note
The prerequisite for this recipe can be obtained from the Getting ready section from the Designing and creating security roles in an OLAP database recipe.
Perform the following steps:
Start SQL Server Management Studio and connect to the SQL Server 2012 Analysis Services instance.
Expand the
Databasesfolder.Choose the AdventureWorksDM database (created in the Getting ready section previously mentioned) and expand the
Rolesfolder.Right-click on the role (here I have selected the DBIA_Processor and DBIA_User roles) to choose Role Properties.
Click on Membership in the Select a page option to Specify the users and groups for this role.
Click on the Add button to be presented with Select Users or Groups on the domain. Here I have selected the DBIA_User role for this step.
Click on the OK button to affect the relevant changes.
Note
If you are not sure about domain names\login names, click on Advanced and Find Now to be presented with Search results for login names and user groups. To display Groups, click on Object Types and then select the types of objects you want to find and ensure Groups is selected.
As the previous steps are defined for the DBIA_User role, now I have chosen two logins and one Windows group to grant privilege for the DBIA_Processor database role.
Click on the OK button to affect the relevant changes.
As we have observed, with most recipes a database role provides all the necessary steps to enable the cube access within that Analysis Services (AS) instance.
Irrespective of the default settings for a database role, the privileges can be assigned to any cube (such as virtual or linked cubes) in the AS database. The recipe steps will grant the specified end users or groups in the database role access to the cube.
By default, the database role will not specify any restriction for the end users in the cube and a certain database role can view all members in the associated cube.
By design the end users in a cube role will be able to view all cells in the associated cube.
When the cube is set with write-enables, and also the role has been granted read/write access to the cube, the end users will be able to update the cube cells. In order to specify certain privileges for each cell, the security process will set relevant steps to define the access.
The security administration comprises many layers to handle, and at the same time continues industry best practices without any compromise. In these following recipes, we will highlight a definitive reference for important aspects of cube security processes by mirroring a quick reference on how best SQL Server 2012 BI features can help to accomplish goals.