SSAS adding members to server administrator role using PowerShell

Question

I am trying to add a member to server administrator role using PowerShell. I am able to add a role and member to each individual database available, but I would like to add it to the top level so that the given user can access the database.

This is what my current code, but I get the error:

Exception calling "Add" with "1" argument(s): "Collection was of a fixed size." At D:\Untitled8.ps1:15 char:1 + $targetsvr.Roles.Members.Add($syncAccount) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : NotSupportedException

Function Add-NewMember
{
[CmdletBinding()]
param(
[Parameter(Position=0,mandatory=$true)]
[string] $ssasInstance,
[Parameter(Position=1,mandatory=$true)]
[string] $syncAccount ="NT Service\SQLSERVERAGENT")

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

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

$targetsvr.Roles.Members.Add($syncAccount)
}
Add-NewMember -ssasInstance "localhost" -syncAccount "NT SERVICE\SQLSERVERAGENT"

Please add the exact error message into the question. Oh, and an user function named as Add-Member is going to collide with Powershell's own functionality too? — vonPryz
docs.microsoft.com/en-us/sql/analysis-services/powershell/… - Doesn't this do what you want basically? I've never tried it personally. — Richard Dakin
That is to add at database level, but I would like to add as per shown in the image — Developer

Jeroen Mostert Jeroen Mostert · Accepted Answer · 2017-04-21T11:19:40

$targetsvr.Roles.Members is a legal expression that results in a collection of all members of all roles (it's equivalent to $targetsvr.Roles | Foreach { $_.Members }). But this collection is synthesized by PowerShell, not an actual member of something, so you can't modify it. You want $targetsvr.Roles["Administrators"].Members instead. And you explicitly need to update the role before the server will see the changes. And you want to give the cmdlet a better name as well. If I may:

Function Add-ASAdministrator {
    param(
        [Parameter(Mandatory=$True, ValueFromPipeline)]
        [string] $User,

        [string] $Instance = "."
    )
    $server = New-Object Microsoft.AnalysisServices.Server
    $server.Connect($Instance)
    $administrators = $server.Roles["Administrators"]
    if ($administrators.Members.Name -notcontains $User) {
        $administrators.Members.Add($User) | Out-Null
        $administrators.Update()
    }
    $server.Disconnect()
}

SSAS adding members to server administrator role using PowerShell

1 Answers