List SSAS User Roles Using PowerShell (Part 2)

A while ago, I have a blog post in regards to using PowerShell to list user roles and also their members. The blog post can be found here. I’ve gotten a few questions about expanding the PowerShell script to:

1. List user roles and their members for all databases within a particular SQL Server Analysis Services (SSAS) server.
2. Output the result into one table

Here’s a PowerShell script that can be use to accomplish those goals

[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)

    # Get the roles available within the SSAS database and loop thru each of them
    foreach ($Role in $SSASDatabase.Roles)
    {
        # Get the members within the role and loop thru each one of them
        foreach ($UserName in $Role.Members)
        {
            # Create a new object that would store the database name, role name and member user name
            $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 UserName -value $UserName.Name

            # Put the item result and append it to the result object
            $Result +=$ItemResult
        }
    }
}

$Result | Select DatabaseName, RoleName, UserName | format-table -auto -wrap | Out-String

List SQL Server Analysis Services Database Properties Using PowerShell

If you have SQL Server Analysis Services (SSAS) and want to list the SSAS databases using PowerShell, here’s a PowerShell script that can do that:

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

# Variable for your SSAS server name
$SSASServerName = "ServerName"

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

# Try to list the SSAS database Name
$SSASServer.Databases | Select Name

It turns out that you can do much more than listing your SSAS database name with this. You can get the SSAS database collation, compatibility level, created date, last processed, etc. To get the full list of properties that are available to you, try to run the following PowerShell script:

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

# Variable for your SSAS server name
$SSASServerName = "ServerName"

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

# Try to list the SSAS database properties that are available to you
$SSASServer.Databases | Get-Member -MemberType property | FT -wrap | Out-String

So now, if I want to get a list of SSAS database name, collation, compatibility level, created data and last processed date, I can run the following PowerShell script:

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

# Variable for your SSAS server name
$SSASServerName = "ServerName"

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

# Try to list the SSAS Database Name, Collation, Compatibility Level, Created Time, Estimated Size, Last Processed Date  
$SSASServer.Databases | Select Name, Collation, CompatibilityLevel, CreatedTimeStamp, EstimatedSize, LastProcessed | FT -wrap | Out-String

Related Blog Post:

List SSAS User Roles Using PowerShell