Automate SSAS Syncing Part 2: Create SSAS Role And Add Member

Part two of this series expands upon yesterdays post, which was creating a database role and assigning permissions, by adding a member to that role. The script adds just one member to the role, but if you need more than one member you can either create a domain group and add all the users required into that group, then use the group name in place of the user name, or do something clever with arrays.

Tomorrow’s post will look at some disk checks that are included to ensure there is enough space to sync cubes.

Here are the links for the other parts of the series
Automate SSAS Syncing Part 1: Create SSAS Database Role
Automate SSAS Syncing Part 2: Create SSAS Role And Add Member

[string] $ssasInstance,
[string] $rolename,
[string] $syncAccount)


$targetsvr = new-Object Microsoft.AnalysisServices.Server

foreach ($db in $targetsvr.Databases)
# Print the Database Name
"Database: " + $db.Name
$role = $null
$role = $db.Roles.FindByName($rolename)
if ($role -eq $null) 
        "Role not found!"
        #let's create the role
        #Create Role
        $roleToCreate = new-Object([Microsoft.AnalysisServices.Role])($rolename)
        “… ‘” + $roleToCreate.Name + “‘ adding to database”

        #add the permissions
        $dbperm = $db.DatabasePermissions.Add($roleToCreate.ID)
        $dbperm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed
        $dbperm.Process = [Microsoft.AnalysisServices.ReadAccess]::Allowed
        $dbperm.Administer = [Microsoft.AnalysisServices.ReadAccess]::Allowed
        $role = $db.Roles.FindByName($rolename)
else {"role "+$newroles.Name } 

#check to see if role that exists has administer permission
        #if not then administer is added
        $dbperm = $db.DatabasePermissions.FindByRole($newroles)
        if ($dbperm.Administer -eq $false)
        $dbperm.Administer = [Microsoft.AnalysisServices.ReadAccess]::Allowed

#part 2

$foundMember = $null
foreach ($member in $role.Members)
if ($member.Name -eq $syncAccount)
$foundMember = $member
If ($foundMember -eq $null)
"      Adding access to " + $syncAccount
$newMem = New-Object Microsoft.AnalysisServices.RoleMember($syncAccount)
"      Usergroup " + $syncAccount + " already added"


Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

6 thoughts on “Automate SSAS Syncing Part 2: Create SSAS Role And Add Member”

  1. 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

    1. Hello!

      I can only guess that either there is no role on the database, or that there is no database that exists.
      I also assume that your code is spread across two lines and not one like in your comment?
      $database = $targetsvr.Databases.FindByName(“MyDbName”)

      1. Hi Richie sorry for the confusion actually when I am redeploying the cube the roles are getting erased it seems so it is always showing null

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s