管理数据库角色和用户Manage database roles and users

在模型数据库级别,所有用户都必须属于一个角色。At the model database level, all users must belong to a role. 角色可针对模型数据库定义具有特定权限的用户。Roles define users with particular permissions for the model database. 添加到角色的任何用户或安全组都必须在与服务器相同的订阅的 Azure AD 租户中具有一个帐户。Any user or security group added to a role must have an account in an Azure AD tenant in the same subscription as the server.

定义角色的方式根据使用的工具有所差异,但效果却是相同的。How you define roles is different depending on the tool you use, but the effect is the same.

角色权限包括:Role permissions include:

  • 管理员 - 用户对数据库具有完全的权限。Administrator - Users have full permissions for the database. 具有管理员权限的数据库角色不同于服务器管理员。Database roles with Administrator permissions are different from server administrators.
  • 处理 - 用户可以连接到数据库并对其执行处理操作,分析模型数据库数据。Process - Users can connect to and perform process operations on the database, and analyze model database data.
  • 读取 - 用户可以使用客户端应用程序连接到模型数据库数据并进行分析。Read - Users can use a client application to connect to and analyze model database data.

创建表格模型项目时,可以通过将 Visual Studio 中的“角色管理器”与 Analysis Services 项目一起使用来创建角色并将用户或组添加到这些角色。When creating a tabular model project, you create roles and add users or groups to those roles by using Role Manager in Visual Studio with Analysis Services projects. 部署到服务器时,可以使用 SQL Server Management Studio (SSMS)、Analysis Services PowerShell cmdlet表格模型脚本语言 (TMSL) 来添加或删除角色和用户成员。When deployed to a server, use SQL Server Management Studio (SSMS), Analysis Services PowerShell cmdlets, or Tabular Model Scripting Language (TMSL) to add or remove roles and user members.

添加“安全组”时,请使用 obj:groupid@tenantidWhen adding a security group, use obj:groupid@tenantid.

在 Visual Studio 中添加或管理角色和用户To add or manage roles and users in Visual Studio

  1. 表格模型资源管理器中,右键单击“角色”。In Tabular Model Explorer, right-click Roles.

  2. 在“角色管理器”中单击“新建”。 In Role Manager, click New.

  3. 键入角色名称。Type a name for the role.

    默认情况下,对于每个新的角色,默认角色名称以递增方式进行编号。By default, the name of the default role is incrementally numbered for each new role. 建议键入可明确标识成员类型的名称,例如,财务经理或人力资源专员。It's recommended you type a name that clearly identifies the member type, for example, Finance Managers or Human Resources Specialists.

  4. 选择以下权限之一:Select one of the following permissions:

    权限Permission 说明Description
    None 成员无法读取或修改模型架构,也无法查询数据。Members cannot read or modify the model schema and cannot query data.
    读取Read 成员可以(基于行筛选器)查询数据,但无法修改模型架构。Members can query data (based on row filters) but cannot modify the model schema.
    读取和处理Read and Process 成员可以(基于行级筛选器)查询数据并运行“处理”和“全部处理”操作,但无法修改模型架构。Members can query data (based on row-level filters) and run Process and Process All operations, but cannot modify the model schema.
    处理Process 成员可以运行“处理”和“全部处理”操作。Members can run Process and Process All operations. 无法读取或修改模型架构,也无法查询数据。Cannot read or modify the model schema and cannot query data.
    管理员Administrator 成员可以修改模型架构并查询所有数据。Members can modify the model schema and query all data.
  5. 如果正在创建的角色具有“读取”或“读取和处理”权限,可以使用 DAX 公式添加行筛选器。If the role you are creating has Read or Read and Process permission, you can add row filters by using a DAX formula. 单击“行筛选器”选项卡,选择表,单击“DAX 筛选器”字段,并键入一个 DAX 公式。Click the Row Filters tab, then select a table, then click the DAX Filter field, and then type a DAX formula.

  6. 单击“成员” > “添加外部成员”Click Members > Add External.

  7. 在“添加外部成员”中,按电子邮件地址输入租户 Azure AD 中的用户或组。In Add External Member, enter users or groups in your tenant Azure AD by email address. 单击“确定”并关闭角色管理器后,角色和角色成员将显示在表格模型资源管理器中。After you click OK and close Role Manager, roles and role members appear in Tabular Model Explorer.

    表格模型资源管理器中的角色和用户

  8. 部署到 Azure Analysis Services 服务器。Deploy to your Azure Analysis Services server.

在 SSMS 中添加或管理角色和用户To add or manage roles and users in SSMS

若要向部署模型数据库添加角色和用户,必须以服务器管理员身份连接到服务器,或已经是具有管理员权限的数据库角色的成员。To add roles and users to a deployed model database, you must be connected to the server as a Server administrator or already in a database role with administrator permissions.

  1. 在对象资源管理器中,右击“角色” > “新建角色”。In Object Exporer, right-click Roles > New Role.

  2. 在“创建角色”中,输入角色名称和说明。In Create Role, enter a role name and description.

  3. 选择权限。Select a permission.

    权限Permission 说明Description
    完全控制(管理员)Full control (Administrator) 成员可以修改模型架构,处理并查询所有数据。Members can modify the model schema, process, and can query all data.
    处理数据库Process database 成员可以运行“处理”和“全部处理”操作。Members can run Process and Process All operations. 无法修改模型架构,也无法查询数据。Cannot modify the model schema and cannot query data.
    读取Read 成员可以(基于行筛选器)查询数据,但无法修改模型架构。Members can query data (based on row filters) but cannot modify the model schema.
  4. 单击“成员资格”,并按电子邮件地址在租户 Azure AD 中输入用户或组。Click Membership, then enter a user or group in your tenant Azure AD by email address.

    添加用户

  5. 如果正在创建的角色具有“读取”权限,可以使用 DAX 公式添加行筛选器。If the role you are creating has Read permission, you can add row filters by using a DAX formula. 单击“行筛选器”,选择表,并在“DAX 筛选器”字段中键入 DAX 公式。Click Row Filters, select a table, and then type a DAX formula in the DAX Filter field.

使用 TMSL 脚本添加角色和用户To add roles and users by using a TMSL script

可在 SSMS 中的 XMLA 窗口中运行 TMSL 脚本或使用 PowerShell。You can run a TMSL script in the XMLA window in SSMS or by using PowerShell. 使用 CreateOrReplace 命令和 Roles 对象。Use the CreateOrReplace command and the Roles object.

示例 TMSL 脚本Sample TMSL script

在此示例中,B2B 外部用户和组添加到了具有 SalesBI 数据库读取权限的 Analyst 角色中。In this sample, a B2B external user and a group are added to the Analyst role with Read permissions for the SalesBI database. 外部用户和组必须均位于相同的租户 Azure AD 中。Both the external user and group must be in same tenant Azure AD.

{
  "createOrReplace": {
    "object": {
      "database": "SalesBI",
      "role": "Analyst"
    },
    "role": {
      "name": "Users",
      "description": "All allowed users to query the model",
      "modelPermission": "read",
      "members": [
        {
          "memberName": "user1@contoso.com",
          "identityProvider": "AzureAD"
        },
        {
          "memberName": "group1@adventureworks.com",
          "identityProvider": "AzureAD"
        }
      ]
    }
  }
}

使用 PowerShell 添加角色和用户To add roles and users by using PowerShell

SqlServer 模块提供任务特定的数据库管理 cmdlet,以及接受表格模型脚本语言 (TMSL) 查询或脚本的通用 Invoke-ASCmd cmdlet。The SqlServer module provides task-specific database management cmdlets and the general-purpose Invoke-ASCmd cmdlet that accepts a Tabular Model Scripting Language (TMSL) query or script. 以下 cmdlet 用于管理数据库角色和用户。The following cmdlets are used for managing database roles and users.

CmdletCmdlet 说明Description
Add-RoleMemberAdd-RoleMember 向数据库角色添加成员。Add a member to a database role.
Remove-RoleMemberRemove-RoleMember 从数据库角色中删除成员。Remove a member from a database role.
Invoke-ASCmdInvoke-ASCmd 执行 TMSL 脚本。Execute a TMSL script.

行筛选器Row filters

行筛选器定义特定角色的成员可以查询表中的哪些行。Row filters define which rows in a table can be queried by members of a particular role. 可使用 DAX 公式为模型中的每个表定义行筛选器。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 can query all rows in the table unless cross-filtering applies from another table.

行筛选器需要 DAX 公式,该公式的求值结果必须为 TRUE/FALSE,以定义该特定角色的成员可以查询的行。Row filters require a DAX formula, which must evaluate to a TRUE/FALSE value, to define the rows that can be queried by members of that particular role. 无法查询未包含在 DAX 公式中的行。Rows not included in the DAX formula cannot be queried. 例如,具有以下行筛选器表达式的 Customers 表: =Customers [Country] = "CHINA" ,Sales 角色的成员只能查看中国境内的客户。For example, the Customers table with the following row filters expression, =Customers [Country] = "CHINA", members of the Sales role can only see customers in the China.

行筛选器适用于指定的行和相关行。Row filters apply to the specified rows and related rows. 如果表具有多个关系,筛选器将对处于活动状态的关系应用安全性。When a table has multiple relationships, filters apply security for the relationship that is active. 行筛选器与为相关表定义的其他行筛选器相交,示例如下:Row filters are intersected with other row filers defined for related tables, for example:

Table DAX 表达式DAX expression
区域Region =Region[Country]="CHINA"=Region[Country]="CHINA"
ProductCategoryProductCategory =ProductCategory[Name]="Bicycles"=ProductCategory[Name]="Bicycles"
事务Transactions =Transactions[Year]=2016=Transactions[Year]=2016

净效果是成员可以查询若干行数据,其中客户位于中国,产品类别为自行车,年份是 2016 年。The net effect is members can query rows of data where the customer is in the China, the product category is bicycles, and the year is 2016. 用户无法查询中国之外的事务、不是自行车的事务或非 2016 年的事务,除非他们属于授予这些权限的另一角色。Users cannot query transactions outside of the China, transactions that are not bicycles, or transactions not in 2016 unless they are a member of another role that grants these permissions.

可以使用筛选器 =FALSE() 拒绝访问整个表的所有行。You can use the filter, =FALSE(), to deny access to all rows for an entire table.

后续步骤Next steps

管理服务器管理员 Manage server administrators
使用 PowerShell 管理 Azure Analysis ServicesManage Azure Analysis Services with PowerShell
表格模型脚本语言 (TMSL) 参考Tabular Model Scripting Language (TMSL) Reference