Using Roles and Permissions
I explained to Ryan that you can define security roles within an Analysis Services database to grant and restrict the rights of users who hold those roles to dimensions, attributes, and measures within cubes. Roles also control who can perform administrative tasks, such as processing cubes or reading cube definitions. You can assign Windows logins and groups to roles to facilitate user access to a database and its cubes.
To create and manage roles, you use Business Intelligence Development Studio or SQL Server Management Studio (SSMS). Both applications offer the following tabs for configuring and managing roles:
- General—Enter a role name and description and an authorization for administrative tasks.
- Membership—Specify the Windows logins and groups that are members of the role.
- Data Sources—Define the role's rights to read data from each data source in the database.
- Cubes—Specify the role's rights to access each cube in a database.
- Cell Data—Define the role's rights to access data within each cube.
- Dimensions—Specify the role's rights to access and process each dimension in the database.
- Dimension Data—Define the role's rights to access members within each dimension.
- Mining Structures—Specify the role's rights to access data mining structures in the database.
The Cell Data and Dimension Data tabs provide the most complex and powerful features, letting you specify with a fine level of granularity the data that members of a role can and can't see. For example, I told Ryan, he could create a security role—Wdlnd Crtr Prod Mgr—for the manager of the Woodland Creatures product type, then use the Cell Data and Dimension Data tabs to let her see only products of that type when she browses the manufacturing cube. In addition, if he wanted her to be able to see only the Accepted Products and Rejected Products measures, he could use the Data Sources and Cubes tabs to grant the Wdlnd Crtr Prod Mgr role read access to the data in the Wry and Wry cube, then use the Cell Data tab to restrict access to the measures in that cube, as Figure 1 shows. The Cell Data tab uses an MDX script expression to restrict the measures the manager can see.
You can use the Dimension Data tab to select the dimensions the Wdlnd Crtr Prod Mgr role can view. I showed Ryan that there are two tabs: Basic and Advanced, as Figure 2 shows. I instructed him to select the Basic tab and click Select all members, then clear the check boxes for the dimension members that the Wdlnd Crtr Prod Mgr role shouldn't have the right to view. Alternatively, he could accomplish the same thing by using the Advanced tab to create appropriate MDX script expressions.
After deploying the role to the Analysis Services database, select the Browser tab to test the new security role. When you browse the cube without using the role, you can see all products and measures. Click the Change User toolbar button to invoke the new role for testing. When the role is in place, you'll see product members from only the Woodland Creatures product type and data for only the Accepted Products and Rejected Products measures, as Figure 3 shows.
Running Right
Within two days of my visit, Ryan Wry had eliminated the wrist rappers and implemented restrictions according to roles. A follow-up inspection revealed no further wrist injuries.
In this case, I tracked down and identified a repulsive security method. In the next case, I suggest a way to track down and identify Reporting Services reports.