To manage security for Analysis Services objects and data, a role is important that associates relevant SIDs of the operating system that will build much more robust system. The Windows users and groups that have specific rights and privileges defined can be incorporated in Analysis Services. There are two different types of roles:
Server role: This is a fixed role for administrator access to Analysis Services instance
Database roles: These are defined by the administrators to manage access to objects and data for users (non-administrator users)
To explain the topic further, a role is a containing object for a collection of users that are classified as members. In this recipe we will overview the steps in creating security roles within Analysis Services database.
The prerequisite for this recipe can be obtained from the Getting ready section in the Designing and creating security roles in an OLAP Database recipe.
Once the above prerequisites are completed follow the steps below:
Open the SSMS and choose Connect to Analysis Services… instance.
Expand SQL Server 2012 instance and the Databases folder.
Choose the database AdventureWorksDM (created within the Getting Ready section from the Designing and creating security roles in an OLAP Database recipe) and expand the Roles folder.
Right-click on the Roles folder to create a new role:
Tip
Until SQL Server 2008 R2, a new role could be created by attempting to right-click on the Role node within the Solution Explorer pane within Business Intelligence Development Studio (BIDS) too. However BIDS tool is deprecated from SQL Server 2012.
To manage and administer the BI technologies, Analysis Services, Integration Services, and Reporting Services, use SQL Server Management Studio tool, which is associated with each of these technologies and is slightly different.
Choose the name of this New Role as
DBIA_Readerand opt for Read definition (as seen in the following screenshot).
The remaining vertical screen-tab options from the Select a page option are explained as follows:
General: The best place to highlight which role is defined for the selected Analysis Services database.
Membership: Choose the corresponding local and domain users/groups to be added for this role.
Data Sources: An essential place to use data mining functionality within Analysis Services. (Unless we have created Data Sources on SSMS Object Explorer, the Data Source screen area will be blank).
Cubes: A place to set up several cube-level permissions (right to access and administrative).
Cell Data: To define security at the cell level within the cube; this is relevant only when the corresponding Cubes option is defined in the preceding option.
Dimensions: This is relevant for all database dimensions level permissions.
Dimension Data: The security that allows Basic and Advanced options for Attributes Hierarchy. You can select or de select all members along with Allowed, Denied and Default member sets.
Once the relevant options are set click on OK to create the specified role within the Analysis Services database.
As you may have observed that the preceding steps confirm the creation of the Reader privileged role, for the sake of multiple recipes specific to Security Roles let us create two additional roles that will be different to each other on the AS database.
Follow these steps for creating two additional roles that will be different to each other on the AS database
Choose the name of this new role as
DBIA_User.Click on OK to create the specified role within the Analysis Services database.
Choose the name of this New Role as
DBIA_Processorand opt for the Process database permission under the Set the database permissions for this role option. (see the preceding screenshot for more information).Click on OK to create the specified role within the Analysis Services database.
The preceding steps enable us to create the necessary security roles that are essential to manage security in Analysis Services objects and data.
The steps to create a specific security role within analytical databases needs elaboration, as it is different from the Server role and the Database role from the relational database world. Similarly, security roles also play very important roles in the SSAS world.
To manage security for Analysis Services objects and data, a role can be associated to the SIDs of Windows operating system users and groups that have specific access rights and permissions defined for the objects managed by SSAS instance.
The two different types of permissions that can be granted to a user are classified as Administrative Security and Data Security. Again such a level of permissions varies from the object of an Analysis Services Database. For instance a user can be granted to execute Local Cube read permissions on the Data Security level and Read Definition on the Administrative Security level.
Security is managed by using roles and permissions. Roles are groups of users and users can be referred to as members. Such members can be added or removed from the roles.
Permissions that are specified by roles can use the objects for which that role has permissions, as they apply to all the members in a role who have equal permissions to the objects. Also, the permissions collection of the object has a single role assigned to it.