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
}
}
}
{
[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
}
}
}
No comments:
Post a Comment