Sunday, September 30, 2012

List SSAS User Roles Using PowerShell

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.

19 comments:

  1. Looks so simple when you know how. Have been looking for this for a while. Many thanks. JK

    ReplyDelete
  2. I'm not able to run this script, is this the entire code?
    Thanks

    ReplyDelete
    Replies
    1. Yes it is the entire code. Are you getting any error when running it?

      Delete
  3. Yes i am getting error ...

    Exception 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

    ReplyDelete
    Replies
    1. Hi Kutbuddin

      Thank 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.

      Delete
  4. Thanks a ton for sharing the code! Very handy

    ReplyDelete
  5. Hi,

    The 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

    ReplyDelete
    Replies
    1. Hi Tarun

      For 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
      }
      }

      Delete
  6. Do this first:
    $FormatEnumerationLimit =-1

    http://exchangeserverpro.com/how-to-prevent-truncation-of-long-output-in-exchange-management-shell/

    ReplyDelete
    Replies
    1. Hi Winstonion

      Thank you for the tips. It should definitely work.

      Lucas

      Delete
  7. You also need to set the screen width to get it to show up

    ReplyDelete
  8. Hi, you can show me an example, for list an Role, Cube Name, Cube Enable for each Role, User

    ReplyDelete
    Replies
    1. Hi Diego

      Certainly. 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

      Delete
  9. I wanna retrieve row filters information of a particular role in a SSAS Database.

    Please help me out.

    ReplyDelete
    Replies
    1. Hi Venkatesh

      There 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

      Delete
  10. should it work for tabulars too? I cant get it working, it doesn't display roles.

    ReplyDelete
  11. Should it work for tabulars too? I cant get it working, it doesn't show the roles (which i am sure do exist)

    ReplyDelete
    Replies
    1. Hi Kojo

      It should work for tabular also. Do you get any error message by any chance?

      Delete
  12. the above code is working fine for SSAS 2014 but it;s not working for SSAS 2017

    ReplyDelete