How to dynamically add/remove role members from SSAS 2016 Tabular model

ssasssas-2016

I am developing a number of tabular models, which currently have a limited number of users assigned to roles within each. At the moment, with the exception of 1 or 2 cases, the majority of the users are "full read" users in all the reports. I am in the process of implementing row-level security in to a number of these roles and I am getting to the point now where, before these models get deployed in to live I think I should be developing a security model to be able to easily add/remove members to/from these roles.

My question is, how can I deploy a model that allows for dynamically adding and removing users, without having to deploy the solution each time.

In my mind my options are as follows:

  1. Do this manually… not dynamic!
  2. Create user groups in AD and add the user groups to the roles. This will be ok for "full read" users (e.g. Directors), or "manager" roles (e.g. sales manager for sales reports), but would not work for users that will be using row-level security (e.g. salesperson).
  3. Create a "global security" table to add to the model, which contains the 'variable' for read access permissions for "full read" access (e.g. Directors and Managers). I will also have another security table that will hold the data for row-level security for other users (e.g. salesperson filters). Then each role in the model will essentially use row-level security based on these security tables.
  4. Use TMSL to process each deployment in the SSAS instance and "createOrReplace" each role deployment where this user exists/needs to be added… unfortunately I don't know any TMSL so I don't know how hard this is to learn?

I want to maintain a clean environment, so when somebody looks at the model in future they don't see users who left years back. Ultimately, when somebody leaves their AD account will be deactivated so this is less of an issue and more of an OCD. However, new members will need to be added.

I'm leaning towards option 3, but are the above options my only choices? Has anybody deployed a different model? Am I missing something obvious!?!?!? I hope not.

Thanks in advance for any help/tips/advice.

EDIT: I have also just thought of using PowerShell to add/remove users. Based on what David said, I could get one of my guys to maintain this instead.

Best Answer

Typically only the role definitions are part of the project. The role memberships are different by environment (dev,test,prod), are usually not administered by the model developers, and so are maintained separately. Then only row-level security is driven from a table in the model.

Related Question