MSAS - Understanding Database Roles

When a new database is created a default role is created for all OLAP Administrators to have access to the Analysis Manager. However, other users have to be given rights to access the database and its objects. Users can be given rights to Browse the cubes and objects but not to use Analysis Manager. If the user has to be given a right to use both the database objects and the Analysis Manager, specific rights have to be assigned.

To assign database roles to users, right click the database and select manage roles… option. The advantage of creating database roles is that it enables the Administrator to restrict access of specific users to specific objects in the database. Even when a new role is created at the cube level, it is a role created at the database level as the same groups and users exist at all levels. A role can be directly created at the database level and applied to the cube. We will create budget analysts role in this section to understand how database roles are created. In the Analysis manager console tree right click the database and select Manage Roles.

The Database Role manager appears.

The Database Role manager is similar to the Cube role manager in appearance. It displays all the roles existing for the database. The All users role created in the Cube role manager is displayed as a database role in the Database role manager. In other words, database roles can be created in the Cube Role manager also. However, there are a number of differences in the role managers.

Click the New button to begin creating new database roles. Type in Budget Analyst in the Role name box and click Add to add a new group role. Select _Budget Analyst group and click Add. Note that the name appears in Add names text box and click Ok.

The user group appears in the database role manager dialog box under users and groups.

The dialog box displays a warning at the bottom that changes will propagate to cube roles based on this role. This is because the database role is global and can be used by one or more cubes within the database. The intrinsic membership role will be the same for all users of the role.

Note that the roles now appear in the Role manager.