T-SQL/SSIS and XMLA script to add users to a Role in SSAS

_{Monday, September 17, 2018 9:01 PM}

Could anyone help me with the script to add users to an already created Role? I would want to get the user list from a SQL table and update membership in the Role. The script would run periodically to refresh the Role membership. The Role membership needs to be cleared and members added for each refresh. (Expecting lot of churn with membership)

I used the Script option to generate the XMLA script to add users(from the existing members from the role). This script seems to require all members to be added at once and not one by one. How do I enhance/correct this to add it one by one? and how do I tie it with T-SQL or in SSIS?

Thanks

_{Tuesday, September 18, 2018 7:48 AM}

Hi Ashfly,

Yes, if you use xmla to add member to a role, you need to give all members at once. The script looks like:

 <ObjectDefinition>
    <Role ….>
      <ID>Role</ID>
      <Name>RoleT</Name>
      <Members>
        <Member>
          <Name>Administrator</Name>
        </Member>
        <Member>
          <Name>user1</Name>
        </Member>
        <Member>
          <Name>user2</Name>
        </Member>
        <Member>
          <Name>user3</Name>
        </Member>
      </Members>
    </Role>
  </ObjectDefinition>
</Alter>

The workaround that you can use PowerShell Script to add member one by one.

The script looks like:

add-role member -membername "test2" -database "WideWorldImportersDW" -rolename "MyFavoriteRole_Copy_V1"

See:

Add SQL Server Analysis Services Role Members via PowerShell or XMLA

As far as I know, T-SQL is not possible, you can call the powershell script in SSIS with Execute Process Task.

Regards,

Pirlo Zhang 

MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

_{Tuesday, September 18, 2018 4:40 PM}

Thanks Pirlo. I was trying to generate dynamic XMLA and was successful to a point. I had issues with generating script when the member count was high. Will try the powershell script option and let you know.

_{Thursday, September 20, 2018 8:14 AM}

Hi Ashfly,

Are there any updates here?

If this issue has been resolved, please mark appropriate replies as answer to close this thread. 

Regards,

Pirlo Zhang 

MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

_{Thursday, September 20, 2018 9:02 PM}

Adding just names shouldn't work, in order for this to work properly you will have to get relevant SID for each user (by running extra query to your AD for SID retrieval)
You can check this similar thread for more details.