By Richie Lee

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


[CmdletBinding()]
param(
[Parameter(Position=0,mandatory=$true)]
[string] $ssasInstance,
[Parameter(Position=1,mandatory=$true)]
[string] $rolename,
[Parameter(Position=2,mandatory=$true)]
[string] $syncAccount)

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

$targetsvr = new-Object Microsoft.AnalysisServices.Server
$targetsvr.Connect($ssasInstance)

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”
    $db.Roles.Add($roleToCreate)
    $roleToCreate.Update()

    
    #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
    $dbperm.Update()
    $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
    $dbperm.Update()
    }

#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)
$role.Members.Add($newMem)
$role.Update()
}
else
{
"   Usergroup " + $syncAccount + " already added"
}

}
$targetsvr.Disconnect()