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.

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:

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