Wednesday, April 29, 2015

PowerShell Script: List SQL Server Analysis Service Role Permission By Database and Cube

A while ago, I wrote a couple of blog entries in regards to using PowerShell scripts in order to list roles within the database and their members. The script can be found here and here.

Let’s try to expand on those scripts further.

List Role Permission For Each Analysis Service Database

If we look at the GUI, on the database level, we can assign the role to have one or more of the following permissions: full control, process database and read definition.

SSAS Role Properties

We can use the following PowerShell script to list role permission for each database:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# SSAS server name variable
$SSASServerName = "ServerName"

# Try to connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Object to store the result
$Result = @()

# Get the SSAS databases and loop thru each of them
foreach ($DB in $SSASServer.Databases)
{
# Get the SSAS database
$SSASDatabase = $SSASServer.Databases.Item($DB.name)

foreach ($Role in $SSASDatabase.Roles)
{
$DatabasePermission = $DB.DatabasePermissions.GetByRole($Role.ID)

$ItemResult = New-Object System.Object
$ItemResult | Add-Member -type NoteProperty -name DatabaseName -value $DB.Name
$ItemResult | Add-Member -type NoteProperty -name RoleName -value $Role.Name
$ItemResult | Add-Member -type NoteProperty -name DatabaseAdministrator -value $DatabasePermission.Administer
$ItemResult | Add-Member -type NoteProperty -name DatabaseProcess -value $DatabasePermission.Process
$ItemResult | Add-Member -type NoteProperty -name DatabaseReadDefinition -value $DatabasePermission.ReadDefinition


$Result +=$ItemResult
}

}

$Result | Select DatabaseName, RoleName, DatabaseAdministrator, DatabaseProcess, DatabaseReadDefinition | format-table * -Wrap -AutoSize | Out-String

List Role Permission For Each Cube within the Analysis Service Database

We can also define role permission for each cube within the Analysis Service database. If we look at the GUI, the basic role permissions for each cube would look like:

image

We can use the following PowerShell script to iterate the permission of each role for each cube within each database:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# SSAS server name variable
$SSASServerName = "ServerName"

# Try to connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Object to store the result
$Result = @()

# Get the SSAS databases and loop thru each of them
foreach ($DB in $SSASServer.Databases)
{
# Get the SSAS database
$SSASDatabase = $SSASServer.Databases.Item($DB.name)

foreach ($Role in $SSASDatabase.Roles)
{

# Get the SSAS cubes within the database
foreach ($Cube in $DB.Cubes)
{
$CubePermission = $Cube.CubePermissions.GetByRole($Role.ID)

$ItemResult = New-Object System.Object
$ItemResult | Add-Member -type NoteProperty -name DatabaseName -value $DB.Name
$ItemResult | Add-Member -type NoteProperty -name RoleName -value $Role.Name
$ItemResult | Add-Member -type NoteProperty -name CubeName -value $Cube.Name
$ItemResult | Add-Member -type NoteProperty -name CubeRead -value $CubePermission.Read.value__
$ItemResult | Add-Member -type NoteProperty -name CubeWrite -value $CubePermission.Write.value__
$ItemResult | Add-Member -type NoteProperty -name CubeProcess -value $CubePermission.Process
$ItemResult | Add-Member -type NoteProperty -name CubeReadDefinition_Local -value $CubePermission.ReadDefinition.value__
$ItemResult | Add-Member -type NoteProperty -name CubeReadSourceData_Drillthrough -value $CubePermission.ReadSourceData.value__

$Result +=$ItemResult
}
}
}

$Result | Select DatabaseName, RoleName, CubeName, CubeRead, CubeWrite, CubeProcess, CubeReadDefinition_Local, CubeReadSourceData_Drillthrough| format-table * -Wrap -AutoSize | Out-String

Combining the Scripts

As a bonus, below is the PowerShell script that can be used to list the role permissions for the cubes and databases within the SQL Server Analysis Services:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$FormatEnumerationLimit = -1

# SSAS server name variable
$SSASServerName = "ServerName"

# Try to connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Object to store the result
$Result = @()

# Get the SSAS databases and loop thru each of them
foreach ($DB in $SSASServer.Databases)
{
# Get the SSAS database
$SSASDatabase = $SSASServer.Databases.Item($DB.name)

foreach ($Role in $SSASDatabase.Roles)
{
$DatabasePermission = $DB.DatabasePermissions.GetByRole($Role.ID)

# Get the SSAS cubes within the database
foreach ($Cube in $DB.Cubes)
{
$CubePermission = $Cube.CubePermissions.GetByRole($Role.ID)

$ItemResult = New-Object System.Object
$ItemResult | Add-Member -type NoteProperty -name DatabaseName -value $DB.Name
$ItemResult | Add-Member -type NoteProperty -name RoleName -value $Role.Name
$ItemResult | Add-Member -type NoteProperty -name DatabaseAdministrator -value $DatabasePermission.Administer
$ItemResult | Add-Member -type NoteProperty -name DatabaseProcess -value $DatabasePermission.Process
$ItemResult | Add-Member -type NoteProperty -name DatabaseReadDefinition -value $DatabasePermission.ReadDefinition
$ItemResult | Add-Member -type NoteProperty -name CubeName -value $Cube.Name
$ItemResult | Add-Member -type NoteProperty -name CubeRead -value $CubePermission.Read.value__
$ItemResult | Add-Member -type NoteProperty -name CubeWrite -value $CubePermission.Write.value__
$ItemResult | Add-Member -type NoteProperty -name CubeProcess -value $CubePermission.Process
$ItemResult | Add-Member -type NoteProperty -name CubeReadDefinition_Local -value $CubePermission.ReadDefinition.value__
$ItemResult | Add-Member -type NoteProperty -name CubeReadSourceData_Drillthrough -value $CubePermission.ReadSourceData.value__
$ItemResult | Add-Member -type NoteProperty -name RoleMembers -value ($Role.Members | Select -ExpandProperty Name)

$Result +=$ItemResult
}
}

}

$Result | Select DatabaseName, RoleName, DatabaseAdministrator, DatabaseProcess, DatabaseReadDefinition, `
CubeName, CubeRead, CubeWrite, CubeProcess, CubeReadDefinition_Local, CubeReadSourceData_Drillthrough, RoleMembers | format-table * -Wrap -AutoSize | Out-String

4 comments:

  1. The first Part Work Perfectly, but the another ones dont work.
    I have SQL Server 2008 R2 Enterprise, thats why?

    ReplyDelete
    Replies
    1. Hi Diego,

      I've only tested the script on SQL 2012. Just wondering, what error are you getting when running the script?

      Thank you
      Lucas

      Delete
  2. the above code is not working for SSAS 2016. where should i change to get list list of users from cube

    ReplyDelete
    Replies
    1. Hi Srinivasarao

      Just wondering, what error that you are getting when running the code in SSAS 2016? Wondering if it is something to do with the CubePermissions.GetByRole?

      If yes, try the following code:

      [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

      $FormatEnumerationLimit = -1

      # SSAS server name variable
      $SSASServerName = "ServerName"

      # Try to connect to the SSAS server
      $SSASServer = New-Object Microsoft.AnalysisServices.Server
      $SSASServer.Connect($SSASServerName)

      # Object to store the result
      $Result = @()

      # Get the SSAS databases and loop thru each of them
      foreach ($DB in $SSASServer.Databases)
      {
      # Get the SSAS database
      $SSASDatabase = $SSASServer.Databases.Item($DB.name)

      foreach ($Role in $SSASDatabase.Roles)
      {
      $DatabasePermission = $DB.DatabasePermissions.GetByRole($Role.ID)

      # Get the SSAS cubes within the database
      foreach ($Cube in $DB.Cubes)
      {
      try
      {
      $CubePermission = $Cube.CubePermissions.GetByRole($Role.ID)
      }
      catch
      {
      }

      $ItemResult = New-Object System.Object
      $ItemResult | Add-Member -type NoteProperty -name DatabaseName -value $DB.Name
      $ItemResult | Add-Member -type NoteProperty -name RoleName -value $Role.Name
      $ItemResult | Add-Member -type NoteProperty -name DatabaseAdministrator -value $DatabasePermission.Administer
      $ItemResult | Add-Member -type NoteProperty -name DatabaseProcess -value $DatabasePermission.Process
      $ItemResult | Add-Member -type NoteProperty -name DatabaseReadDefinition -value $DatabasePermission.ReadDefinition
      $ItemResult | Add-Member -type NoteProperty -name CubeName -value $Cube.Name
      $ItemResult | Add-Member -type NoteProperty -name CubeRead -value $CubePermission.Read.value__
      $ItemResult | Add-Member -type NoteProperty -name CubeWrite -value $CubePermission.Write.value__
      $ItemResult | Add-Member -type NoteProperty -name CubeProcess -value $CubePermission.Process
      $ItemResult | Add-Member -type NoteProperty -name CubeReadDefinition_Local -value $CubePermission.ReadDefinition.value__
      $ItemResult | Add-Member -type NoteProperty -name CubeReadSourceData_Drillthrough -value $CubePermission.ReadSourceData.value__
      $ItemResult | Add-Member -type NoteProperty -name RoleMembers -value ($Role.Members | Select -ExpandProperty Name)

      $Result +=$ItemResult
      }
      }

      }

      $Result | Select DatabaseName, RoleName, DatabaseAdministrator, DatabaseProcess, DatabaseReadDefinition, `
      CubeName, CubeRead, CubeWrite, CubeProcess, CubeReadDefinition_Local, CubeReadSourceData_Drillthrough, RoleMembers | format-table * -Wrap -AutoSize | Out-String

      Thank you
      Lucas

      Delete