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:

List user roles and their members for all databases within a particular SQL Server Analysis Services (SSAS) server.

# Get the SSAS databases and loop thru each of themforeach ($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 } }}