Create and manage roles in SSMS

Article
11/10/2023

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

This article describes how to use SQL Server Management Studio (SSMS) to create roles, define role permissions, and add users for deployed tabular models or Power BI Premium semantic models. To learn about using Visual Studio to create and manage roles for tabular model projects, see Create and manage roles in Visual Studio.

Use SSMS

To create a new role

In SQL Server Management Studio, expand the tabular model database for which you want to create a new role, then right click on Roles, and then click New Role.
In the Create Role dialog box, in the Select a page window, click General.
In the general settings window, in the Name field, type a name for the role.

Use names that clearly identify the member type, for example, Finance Managers or Human Resources Specialists, and be sure the name doesn't include a comma. By default, the name of the default role will be incrementally numbered for each new role.

In Set the database permissions for this role, select one of the following permissions options:

Permission	Description
Full control (Administrator)	Members can make modifications to the model schema and can view all data.
Process database	Members can run Process and Process All operations. Cannot modify the model schema and cannot view data.
Read	Members are allowed to view data (based on row filters) but cannot make any changes to the model schema.

In the Create Role dialog box, in the Select a page window, click Membership.
In the membership settings window, click Add, and then in the Select Users or Groups dialog box, add users or groups you want to add as members.
If the role you are creating has Read permissions, you can add row filters for any table by using a DAX formula. To add row filters, in the Role Properties - <rolename> dialog box, in Select a page, click on Row Filters.
In the row filters window, select a table, then click on the DAX Filter field, and then in the DAX Filter - <tablename> field, type a DAX formula.

Note

The DAX Filter - <tablename> field does not contain an AutoComplete query editor or insert function feature.
Click Ok to save the role.

To copy a role

In SQL Server Management Studio, expand the tabular model database that contains the role you want to copy, then expand Roles, then right click on the role, and then click Duplicate.

To edit a role

In SQL Server Management Studio, expand the tabular model database that contains the role you want to edit, then expand Roles, then right click on the role, and then click Properties.

In the Role Properties <rolename> dialog box, you can change permissions, add or remove members, and add/edit row filters.

To delete a role

In SQL Server Management Studio, expand the tabular model database that contains the role you want to delete, then expand Roles, then right click on the role, and then click Delete.

Script

Roles for deployed models and semantic models can be scripted by using Tabular Model Scripting Language (TMSL) to create or modify the Roles object. TMSL scripts can be executed in SSMS or with the Invoke-ASCmd PowerShell cmdlet.

Right-click the database object > Script > Script database as > CREATE or REPLACE To > New Query Editor Window. Roles are defined in the roles object, for example:

        "roles": [
          {
            "name": "Sales Manager",
            "modelPermission": "read"
          },
          {
            "name": "Sales Analyst US",
            "modelPermission": "read",
            "tablePermissions": [
              {
                "name": "DimGeography",
                "filterExpression": "DimGeography[CountryRegionCode] = \"US\" "
              }
            ]
          }
        ],

Share via