SSAS: Powershell to replace a group member in a role

There was a question in the SSAS forum recently on how to replace one group name with another within the membership of a number of SSAS roles in a number of databases. While you could possibly do this with XMLA it would be tricky as you have to re-submit the whole membership list, you can't just add/remove single members. The easiest way to do this is to write something using the AMO library and in my opinion the easiest way to write a script for AMO is using Powershell.

Below is my short script which loops through all roles in all databases on the server and swaps out one group or user with another. I tried to make the script verbose and readable and I added some strings which are echoed out to the console so that you can see the roles and members that the script is iterating over.



$svr = new-Object Microsoft.AnalysisServices.Server

foreach ($db in $svr.Databases)
# Print the Database Name
"Database: " + $db.Name
foreach ($role in $db.Roles)
    $foundMember = $null
    # Print the Role Name
    "   Role: " + $role.Name    #Print the
    foreach ($member in $role.Members)
     # Print the member name(s)
      "      " + $member.Name
      if ($member.Name -eq "domain_name\old_group_name")
        $foundMember = $member
    If ($foundMember -ne $null)
      "    Member Found!"
      $newRole = New-Object Microsoft.AnalysisServices.RoleMember("domain_name\new_group_name")

Print | posted on Thursday, June 11, 2009 8:41 AM