Using SSAS roles impersonation

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:

Set up roles in SSAS and restrict access to data accordingly for each role.

Create user groups in Dundas BI that correspond exactly to the SSAS roles.

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.

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).