Using SSAS roles impersonation

Contents[Hide]

1. Overview

SQL Server Analysis Services (SSAS) relies on Windows authentication and roles  to determine user permissions. When you create a new data connector in Dundas BI using the SSAS data provider, you have the option of connecting using Roles Impersonation, which allows you to control access to cube data on a per-user basis. Users will see different data displayed on dashboards, for example, depending on the Dundas BI user group (and corresponding SSAS role) they belong to.

The idea behind roles impersonation is simple and basically consists of three parts:

  1. Set up roles in SSAS and restrict access to data accordingly for each role.
  2. Create user groups in Dundas BI that correspond exactly to the SSAS roles.
  3. Connect to SSAS using the Roles impersonation option.

For more information about SSAS roles, see Roles and Permissions (Analysis Services) in Microsoft's documentation.

2. Set up roles in SSAS

Launch SQL Server Management Studio and connect to Analysis Services.

Expand your SSAS database and right-click on the Roles folder. From the menu, select New Role.

Create a new role in SSAS
Create a new role in SSAS

In the General page of the Create Role dialog, set the following fields:

  • Role name: Role A
  • Set the database permissions for this role: Read definition

Set the name and database permission for Role A
Set the name and database permission for Role A

Go to the Cubes page of the Create Role dialog and give read-access to a specific cube.

Give read-access for a specific cube to Role A
Give read-access for a specific cube to Role A

In the Dimension Data page, restrict access to cube data for this role. For example:

  • Select the Customer dimension.
  • Select the Country attribute hierarchy.
  • Uncheck all members except United States.

Restrict access to data for Role A
Restrict access to data for Role A

Note
When using role impersonation on a native OLAP cube, the user will see the totals for All the members in the cube. If you want the totals to be the aggregate for only the visible cell values, open the Advanced tab in the Dimension Data page and enable the Enable Visual Totals option.

In the Membership page, add a domain user to the role. This will be the Windows credentials needed when creating the data connector in Dundas BI.

Add a domain user to Role A
Add a domain user to Role A

Click OK in the Create Role dialog to finish creating Role A.

Next, follow the same steps as above to create Role B. The only difference is in the Dimension Data page where you'll want to restrict access to different cube data for the role. For example:

  • Select the Customer dimension.
  • Select the Country attribute hierarchy.
  • Check all members except United States.

Restrict access to data for Role B
Restrict access to data for Role B

Refresh the SSAS database node to see the two roles.

Role A and Role B have been added
Role A and Role B have been added

3. Set up user groups and accounts in Dundas BI

Log on to Dundas BI as an administrator and go to the Admin screen. Expand Account Service and click Groups.

For each SSAS role, create a corresponding group in Dundas BI with the exact same name as the role.

Note that role/group names cannot include any characters from this list: 

. , ; ' ` : / \ * | ? " % $ ! + = ( ) [ ] { } < >

The underscore (_) character is allowed.

Create Dundas BI groups for Role A and Role B
Create Dundas BI groups for Role A and Role B

Next, create a new user account named User A and add this as a member of the group Role A.

Similarly, create a new user account named User B and add this as a member of the group Role B.

4. Create a new project and dashboard

Log on to Dundas BI as an administrator or developer and create a new project (Project1) which will be accessible to all users. Make sure Project1 is the active project.

Create a new data connector (DataConnector1) using the SQL Server Analysis Services data provider:

  • Set Windows Impersonation to Specified and enter the domain credentials that were added to the SSAS Membership pages.
  • Set the Database Name to the name of the SSAS database.
  • Set the Impersonation field to Roles.
  • Leave the Script field at its default setting. This C# script returns the names of the Dundas BI groups the current user belongs to (excluding built-in Dundas BI groups such as System Administrators and Everyone).

Create a new data connector using the Roles option
Create a new data connector using the Roles option

Then create a new dashboard (Dashboard1) by dragging the Internet Sales Amount measure and the [Customer].[Customer Geography] hierarchy from DataConnector1 to the dashboard canvas.

Create a new Dashboard1
Create a new Dashboard1

Right click on Dashboard1 in the Explore window and check it in.

5. View the dashboard as each user

Log out of Dundas BI and log back on as UserA

View Dashboard1 and observe that it displays restricted data corresponding to SSAS Role A.

User A sees data according to Role A
User A sees data according to Role A

Log out again and log back on as UserB.

View Dashboard1 and observe that it displays restricted data corresponding to SSAS Role B.

User B sees data according to Role B
User B sees data according to Role B

6. See also

 

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri