Sunday, June 9, 2013

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

2 comments:

  1. Thanks a bunch for the code. It works perfectly except for one typo:

    $ItemResult | Add-Member -type NoteProperty -name DatabaseName -value $DBName.Name

    Should be

    $ItemResult | Add-Member -type NoteProperty -name DatabaseName -value $DB.Name

    ReplyDelete
    Replies
    1. You're right. Thank you for that. I have updated the code in the blog.

      Delete