Connect to your specific SQL analysis instance and go through properties to security.
You should see that there is no Service Agent in the list of Server Administrators.
Figure 1 - Before
After executing my PowerShell script (below), the agent has been added.
Figure 2 - After script
The following PowerShell function will get the SQL service logon name associated with the specified computer and will add that user to the Analysis Server 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
}
}
Here are some examples of the above script in use. Check the instance after running to ensure the user has been added correctly (as indicated in Figure 2 above)
Example 1 - Default SQL and Analysis instance
Add-SqlServiceLogonAccount -SqlServerInstance "localhost" -AnalysisServerInstance "localhost"
Example 2 - Named SQL and Analysis instance
Add-SqlServiceLogonAccount -SqlServerInstance "pc-name\local" -AnalysisServerInstance "pc-name\local"
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
https://www.experts-exchange.com/questions/29208388/Implement-SSAS-Tabular-Model-Row-Level-Security-for-non-domain-users.html