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.

 

[System.reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect("localhost\sql08")

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!"
      $role.Members.Remove($foundMember)
      $newRole = New-Object Microsoft.AnalysisServices.RoleMember("domain_name\new_group_name")
      $role.Members.Add($newRole)
      $role.Update()
    }
  }
}
$svr.Disconnect()

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

Comments on this post

# re: SSAS: Powershell to replace a group member in a role

Requesting Gravatar...
Hello when I am filtering the database as follows

$database = $targetsvr.Databases.FindByName("MyDbName") $database.Roles

I am getting roles as empty how to resolve this
Left by Dorababu on Apr 21, 2017 4:55 PM

# re: SSAS: Powershell to replace a group member in a role

Requesting Gravatar...
That probably means either FindByName did not find a database or the database does not have any roles. Try echoing out the contents of the $database object to see if it's populated.
Left by Darren Gosbell on Apr 21, 2017 5:55 PM

Your comment:

 (will show your gravatar)