(v24) Using SSAS Roles Impersonation

Using SSAS Roles Impersonation

This applies to: Managed Dashboards, Managed Reports

SQL Server Analysis Services (SSAS) relies on Windows authentication and roles to determine user permissions. When you create a new data connector 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 Symphony 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 Symphony that correspond exactly to the SSAS roles.

• Connect to SSAS using the Roles impersonation option.

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

This topic contains the following sections:

• Set Up Roles in SSAS

• Set Up User Groups and Accounts in Symphony

• Create a New Project and Dashboard

• View the Dashboard as Each User

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.

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

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

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.

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

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

Refresh the SSAS database node to see the two roles.

Set up User Groups and Accounts in Symphony

Log on toSymphony as an administrator and go to the Admin screen. Expand Account Service and select Groups.

For each SSAS role, create a corresponding group in Symphony 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.

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

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

Create a New Project and Dashboard

Log on to Symphony 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 groups the current user belongs to (excluding built-in Symphony groups such as System Administrators and Everyone).

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.

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

View the Dashboard as Each User

Log out of Symphony and log back on as UserA.

View Dashboard1 and observe that it displays restricted data corresponding to SSAS 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.