- Instant SQL Server Analysis Services 2012 Cube Security
- Satya SK Jayanty
- 470字
- 2021-08-13 16:32:04
How to do it...
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.