Showing posts with label SSAS. Show all posts
Showing posts with label SSAS. Show all posts

Friday, April 21, 2017

Adding Sql Server agent service logon as administrator to SSAS

In this Power Shell script I will explain how we can add SQL server agent service logon to SSAS administrator group

Function Add-SqlServiceLogonAccount
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)]
        [string] $SqlServerInstance = $env:computername,
        [parameter(Mandatory=$true)]
        [string]  $AnalysisServerInstance,
        $ComputerName = $env:computername
    )
        try
        {
            $ValidAnalysis = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-String

            if([string]::IsNullOrWhitespace($ValidAnalysis))
            {
                throw "Unable to find either Microsoft.AnalysisServices.AdomdClient or Microsoft.AnalysisServices in GAC"
            }

            $SqlInstance =  $SqlServerInstance.Split("\")
            #Getting the required SQL services running for both default localhost and as well for new Instances
            if($SqlServerInstance -match "\\")
            {
                $SqlInstance = "SQLAgent`$$($SqlInstance[1])"
            }
            else
            {
                $SqlInstance = "SQLSERVERAGENT"
                $SqlServiceDetails = Get-WmiObject -Class Win32_Service -ComputerName $ComputerName |
                               select name,DisplayName, StartName, State |
                               Where {$_.name -eq "$SqlInstance"  -and $_.State -eq "Running" }          
            }

            [string]$loginName = [string]::Empty

            if($SqlServiceDetails -ne $null)
            {
                if($SqlServiceDetails.Name -eq $SqlInstance)
                {
                    $loginName = $SqlServiceDetails.StartName
                }
            }

            if(![string]::IsNullOrWhitespace($loginName))
            {
                $Targetserver = new-Object Microsoft.AnalysisServices.Server
                $Targetserver.Connect($AnalysisServerInstance)

                #Getting members under the role Administrators
                $administrators = $Targetserver.Roles["Administrators"]
                #checking for the existence of loginname, if not exists adding member to Administrators group
                if ($administrators.Members.Name -notcontains $loginName) {
                    Write-Host "Adding the agent logon account $loginName to the Administrators group"
                    $administrators.Members.Add($loginName) | Out-Null
                    $administrators.Update()
                    Write-Host "Adding the agent logon account $loginName to the Administrators group"
                }
                else
                {
                    Write-Verbose "$loginName was already added to the Administrators group"
                }
                $Targetserver.Disconnect()
            }
        }
        catch
        {
            throw $_.Message
        }
    }
}

Popular Posts