Is it possible to have different detail row expressions for different users based on security in SSAS tabular?

Question

I'm trying to implement different detail row expressions (DRE) on the same measure based on the user account currently using the model. For example: I have a measure based on a table with Patient information. User X is allowed to see all columns in the DRE. User Y is allowed to see all columns except for the patient_ID and patient_name.

Is it possible to implement something like this? Preferably based on roles and not on specific user accounts. I've searched online and can't seem to find any functionality like this.

I'm using Visual Studio 2017 SSAS Tabular with compatibility level: SQL Server 2017 / Azure Analysis Services (1400)

Answer 1

On the role level, you can indicate in the settings what data the user has access, but it works on the level of the entire row, not individual columns.

I haven't tested it, but maybe you can get the correct result using USERNAME () or USERPRINCIPALNAME (); I imagine you could create an additional "Authorized" table containing the username.

And in the calculation itself, refer to it on the basis of IF (USERNAME () in 'Authorized' [USERNAME], "Full access", "Limited access")

EDIT: in 2017, Object-Level-Security was introduced.

https://learn.microsoft.com/en-us/analysis-services/tabular-models/object-level-security?view=asallproducts-allversions#column-level-security

Similar to table-level security, with column-level security you can not only restrict access to column data, but also sensitive column names, helping prevent malicious users from discovering a column.

Column-level security is set in the JSON-based metadata in the Model.bim, Tabular Model Scripting Language (TMSL), or Tabular Object Model (TOM). Set the metadataPermission property of the columnPermissions class in the Roles object to none.

In this example, the metadataPermission property of the columnPermissions class for the Base Rate column in the Employees table is set to none:

"roles": [
  {
    "name": "Users",
    "description": "All allowed users to query the model",
    "modelPermission": "read",
    "tablePermissions": [
      {
        "name": "Employee",
        "columnPermissions": [
          {
            "name": "Base Rate",
            "metadataPermission": "none"
          }
        ]
      }
    ]
  }