SSAS: Powershell to replace a group member in a role

[Darren Gosbell]

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()






More...