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

2 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