Here’s the PowerShell script that can be use to list users and their roles in SQL Server Analysis Service (SSAS) database using PowerShell and Analysis Management Object (AMO):
[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$SSASServerName = "ServerName"
$SSASDB = "DatabaseName"
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)
$SSASDatabase = $SSASServer.Databases.Item($SSASDB)
$SSASDatabase.Roles | Select Name, Members
Notes (25th Apr 2013): Made a correction to one of the variable name on the code. On line 8, instead of using $SSASServer, it should use $SSASDB.
Looks so simple when you know how. Have been looking for this for a while. Many thanks. JK
ReplyDeleteI'm not able to run this script, is this the entire code?
ReplyDeleteThanks
Yes it is the entire code. Are you getting any error when running it?
DeleteYes i am getting error ...
ReplyDeleteException getting "Item": "Exception calling "get_Item" with "1" argument(s): "
The 'Database' with 'ID' = 'ServerName\InstanceNAme' doesn't exist in the collecti
on.""
At line:1 char:43
+ $SSASDatabase = $SSASServer.Databases.Item <<<< ($SSASServer)
+ CategoryInfo : NotSpecified: (:) [], GetValueInvocationExceptio
n
+ FullyQualifiedErrorId : CatchFromBaseParameterizedPropertyAdapterGetValu
e
Hi Kutbuddin
DeleteThank you for the comment. I just reviewed my code, it looks like I use the wrong variable on one of the line. Here's the revised code:
[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$SSASServerName = "ServerName"
$SSASDB = "DatabaseName"
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)
$SSASDatabase = $SSASServer.Databases.Item($SSASDB)
$SSASDatabase.Roles | Select Name, Members
I also have updated the blog post to reflect the correct code.
Thanks a ton for sharing the code! Very handy
ReplyDeleteHi,
ReplyDeleteThe members in output are getting truncated. Could you please let us know how to fix this?
Also, I need to get this detail from each of the SSAS DB. Could you provide a version of this code that provides the same detail for each DB
Hi Tarun
DeleteFor the output, you can use the solution that winstonion suggested. Having the $FormatEnumerationLimit = -1 should do the trick.
In addition, if you have mutiple database and you want to get the detail for each of the database, one thing that you can do is to get the database list and then do a foreach loop.
A sample code for this would be:
[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$FormatEnumerationLimit = -1
$SSASServerName = "ServerName"
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)
foreach ($DB in $SSASServer.Databases)
{
$SSASDatabase = $SSASServer.Databases.Item($DB.name)
foreach ($Role in $SSASDatabase.Roles)
{
$Role.Members | Select @{Name="DatabaseName"; Expression={$DBName.name}}, @{Name="RoleName"; Expression={$Role.Name}}, Name | format-table -auto -wrap | Out-String
}
}
Do this first:
ReplyDelete$FormatEnumerationLimit =-1
http://exchangeserverpro.com/how-to-prevent-truncation-of-long-output-in-exchange-management-shell/
Hi Winstonion
DeleteThank you for the tips. It should definitely work.
Lucas
You also need to set the screen width to get it to show up
ReplyDeleteHi, you can show me an example, for list an Role, Cube Name, Cube Enable for each Role, User
ReplyDeleteHi Diego
DeleteCertainly. I have created some examples. It can be found on:
http://www.lucasnotes.com/2015/04/powershell-script-list-sql-server.html
Hopefully it is close to what you are looking for.
Thank you
I wanna retrieve row filters information of a particular role in a SSAS Database.
ReplyDeletePlease help me out.
Hi Venkatesh
DeleteThere are several ways where you can do this.
1) You can use the GetByName() method.
[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$SSASServerName = "ServerName"
$SSASDB = "DatabaseName"
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)
$SSASDatabase = $SSASServer.Databases.Item($SSASDB)
$SSASDatabase.Roles.GetByName("RoleName") | Select Name, Members
2) Use the where clause on the PowerShell to filter the Role
[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$SSASServerName = "ServerName"
$SSASDB = "DatabaseName"
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)
$SSASDatabase = $SSASServer.Databases.Item($SSASDB)
$SSASDatabase.Roles | Select Name, Members | Where {$_.name -eq "RoleName"}
3) You can also output the PowerShell to GridView. One of the neat thing about GridView is that there is a filtering capability.
[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$SSASServerName = "ServerName"
$SSASDB = "DatabaseName"
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)
$SSASDatabase = $SSASServer.Databases.Item($SSASDB)
$SSASDatabase.Roles | Select Name, Members | Out-GridView
should it work for tabulars too? I cant get it working, it doesn't display roles.
ReplyDeleteShould it work for tabulars too? I cant get it working, it doesn't show the roles (which i am sure do exist)
ReplyDeleteHi Kojo
DeleteIt should work for tabular also. Do you get any error message by any chance?
the above code is working fine for SSAS 2014 but it;s not working for SSAS 2017
ReplyDelete