Roles (SSAS Tabular)

Roles, in tabular models, define member permissions for a model. Each role contains members, by Windows username or by Windows group, and permissions (read, process, administrator). Members of the role can perform actions on the model as defined by the role permission. Roles defined with read permissions can also provide additional security at the row-level by using row-level filters.

Information in this topic is meant for tabular model authors who define roles by using the Role Manager dialog box in SQL Server Data Tools (SSDT). Roles defined during model authoring apply to the model workspace database. After a model database has been deployed, model database administrators can manage (add, edit, delete) role members using SQL Server Management Studio. To learn about managing members of roles in a deployed database, see Tabular Model Roles (SSAS Tabular).

The Tabular Modeling (Adventure Works Tutorial) includes additional information and lessons on how to use this feature.

Sections in this topic:

• Understanding Roles

• Permissions

• Row Filters

• Testing Roles

• Related Tasks

Understanding Roles

Roles are used in Microsoft SQL Server Analysis Services to manage security for Analysis Services and data. There are two types of roles in Analysis Services:

• The server role, a fixed role that provides administrator access to an instance of Analysis Services.

• Database roles, roles defined by model authors and administrators to control access to a model database and data for non-administrator users.

Roles defined for a tabular model are database roles. That is, the roles contain members consisting of Windows users or groups that have specific permissions that define the action those members can take on the model database. A database role is created as a separate object in the database, and applies only to the database in which that role is created. Windows users and/or Windows groups are included in the role by the model author, which, by default, has Administrator permissions on the workspace database server; for a deployed model, by an administrator.

Roles in tabular models can be further defined with row filters. Row filters use DAX expressions to define the rows in a table, and any related rows in the many direction, that a user can query. Row filters using DAX expressions can only be defined for the Read and Read and Process permissions. For more information, see Row filters later in this topic.

By default, when you create a new tabular model project, the model project does not have any roles. Roles can be defined by using the Role Manager dialog box in SQL Server Data Tools. When roles are defined during model authoring, they are applied to the model workspace database. When the model is deployed, the same roles are applied to the deployed model. After a model has been deployed, members of the server role (Analysis Services Administrator) and database administrators can manage the roles associated with the model and the members associated with each role by using SQL Server Management Studio.

Permissions

Each role has a single defined database permission (except for the combined Read and Process permission). By default, a new role will have the None permission. That is, once members are added to the role with the None permission, they cannot modify the database, run a process operation, query data, or see the database unless a different permission is granted.

A Windows group or user can be a member of any number of roles, each role with a different permission. When a user is a member of multiple roles, the permissions defined for each role are cumulative. For example, if a user is a member of a role with the Read permission, and also a member of a role with None permission, that user will have Read permissions.

Each role can have one the following permissions defined:

Row Filters

Row filters define which rows in a table can be queried by members of a particular role. Row filters are defined for each table in a model by using DAX formulas.

Row filters can be defined only for roles with Read and Read and Process permissions. By default, if a row filter is not defined for a particular table, members of a role that has Read or Read and Process permission can query all rows in the table unless cross-filtering applies from another table.

Once a row filter is defined for a particular table, a DAX formula, which must evaluate to a TRUE/FALSE value, defines the rows that can be queried by members of that particular role. Rows not included in the DAX formula will cannot be queried. For example, for members of the Sales role, the Customers table with the following row filters expression, =Customers [Country] = “USA”, members of the Sales role, will only be able to see customers in the USA.

Row filters apply to the specified rows as well as related rows. When a table has multiple relationships, filters apply security for the relationship that is active. Row filters will be intersected with other row filers defined for related tables, for example:

The net effect of these permissions on the Transactions table is that members will be allowed to query rows of data where the customer is in the USA, and the product category is bicycles, and the year is 2008. Users would not be able to query any transactions outside of the USA, or any transactions that are not bicycles, or any transactions not in 2008 unless they are a member of another role that grants these permissions.

You can use the filter, =FALSE(), to deny access to all rows for an entire table.

Dynamic Security

Dynamic security provides a way to define row level security based on the user name of the user currently logged on or the CustomData property returned from a connection string. In order to implement dynamic security, you must include in your model a table with login (Windows user name) values for users as well as a field that can be used to define a particular permission; for example, a dimEmployees table with a login ID (domain\username) as well as a department value for each employee.

To implement dynamic security, you can use the following functions as part of a DAX formula to return the user name of the user currently logged on, or the CustomData property in a connection string:

You can use the LOOKUPVALUE function to return values for a column in which the Windows user name is the same as the user name returned by the USERNAME function or a string returned by the CustomData function. Queries can then be restricted where the values returned by LOOKUPVALUE match values in the same or related table.

For example, using this formula:

='dimDepartmentGroup'[DepartmentGroupId]=LOOKUPVALUE('dimEmployees'[DepartmentGroupId], 'dimEmployees'[LoginId], USERNAME(), 'dimEmployees'[LoginId], 'dimDepartmentGroup'[DepartmentGroupId])

The LOOKUPVALUE function returns values for the dimEmployees[DepartmentId] column where the dimEmployees[LoginId] is the same as the LoginID of the user currently logged on, returned by USERNAME, and values for dimEmployees[DepartmentId] are the same as values for dimDepartmentGroup[DepartmentId]. The values in DepartmentId returned by LOOKUPVALUE are then used to restrict the rows queried in the dimDepartment table, and any tables related by DepartmentId. Only rows where DepartmentId are also in the values for the DepartmentId returned by LOOKUPVALUE function are returned.

dimEmployees

dimDepartment

Testing Roles

When authoring a model project, you can use the Analyze in Excel feature to test the efficacy of the roles you have defined. From the Model menu in the model designer, when you click Analyze in Excel, before Excel opens, the Choose Credentials and Perspective dialog box appears. In this dialog, you can specify the current username, a different username, a role, and a perspective with which you will use to connect to the workspace model as a data source. For more information, see Analyze in Excel (SSAS Tabular).

Related Tasks

See Also

Reference

USERNAME Function (DAX)

LOOKUPVALUE Function (DAX)

CUSTOMDATA Function (DAX)

Concepts

Perspectives (SSAS Tabular)

Analyze in Excel (SSAS Tabular)