Assigning user groups to the Analysis Database Roles using PowerShell

One of the steps involved when installing a full-fledged Dynamics AX 2009 installation is the setting up the security schema for OLAP database. If you do not grant the domain users access to the predefined roles, they will not be able to load the Analysis Reports and Role Center KPI's. When you run the reports and test the Role Centers as administrator, you are essentially bypassing these security checks. You need to test using a regular user to make sure everything is working as expected.

In most cases I end up with simply granting either Domain Users or one specific user group access to all the predefined roles. If you really want to harden security, you would create one Active Directory Group for each role and add users to those roles.

The manual solution

Now adding user groups to each of these roles is a tedious task. The procedure is described on MSDN, but I'm going to repeat the steps here:

1. Open SQL Server Management Studio
2. Connect to the Analysis Services
3. Open the Dynamics AX OLAP Database
4. Open the Roles
5. For each role, repeat these steps
1. Right-click and choose Properties
2. Open Memberships
3. Click Add
4. Click Object Types and make sure "Groups" is marked. Press OK.
5. Click Locations and navigate to the Active Directory you will search in. Press OK.
6. Write on the name of the group you want to add. For example "Domain Users" is a system group that includes all registered domain users.
7. Click Search Names so it validates your input.
8. Click OK
9. Click OK again in the Edit Role Dialog. 

There are 26 roles in my test environment at the moment. Having to add these Memberships will take me about 10 minutes or so, but is extremely boring. 

A simpler solution - PowerShell

Just recently I was about to do this one more time, but then I started thinking about solving this with PowerShell.
PowerShell will be an important part of Dynamics AX 2012, since most of the installation, maintenance and configuration will available with PowerShell commands. So I might as well start to get PowerShell in my fingers.

I searched on the Internet and came across this article by Darren Gosbell about replacing group members from roles in SSAS. By taking his example, I made some minor modifications and here is my script for adding one group to all roles:

[System.reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") 

$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect("localhost") 
$axssasgrp = "CONTOSO\Domain Users"
foreach ($db in $svr.Databases)
{
    # Print the Database Name
    "Database: " + $db.Name
    If ($db.Name -eq "Dynamics AX")
    {
        foreach ($role in $db.Roles)
        {
            "   Role: " + $role.Name    
            $foundMember = $null
            foreach ($member in $role.Members)
            {
                if ($member.Name -eq $axssasgrp)
                {
                    $foundMember = $member
                }
            }
            If ($foundMember -eq $null)
            {
                "      Adding access to " + $axssasgrp
                $newRole = New-Object Microsoft.AnalysisServices.RoleMember($axssasgrp)
                $role.Members.Add($newRole)
                $role.Update()
            }
            else
            {
                "      Usergroup " + $axssasgrp + " already added"
            }
        }
    }
}
$svr.Disconnect()

Now you might need to run PowerShell as administrator for this to work. Change the server name and user group name to make it work in your scenario. I never change the name of the Dynamics AX OLAP.

For cleaning up I also made another script that removes a usergroup. This script looks like this:

[System.reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") 

$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect("localhost") 
$axssasgrp = "CONTOSO\Domain Users"
foreach ($db in $svr.Databases)
{
    # Print the Database Name
    "Database: " + $db.Name
    If ($db.Name -eq "Dynamics AX")
    {
        foreach ($role in $db.Roles)
        {
            "   Role: " + $role.Name    
            $foundMember = $null
            foreach ($member in $role.Members)
            {
                if ($member.Name -eq $axssasgrp)
                {
                    $foundMember = $member
                }
            }
            If ($foundMember -ne $null)
            {
                "      Removing access to " + $axssasgrp
                $role.Members.Remove($foundMember)
                $role.Update()
            }
            else
            {
                "      Usergroup " + $axssasgrp + " not found in role"
            }
        }
    }
}
$svr.Disconnect()

Now I would like to improve this script by adding the ability to read groups from a CSV-file where you could differentiate what user group gets added to what role. I am just learning PowerShell, so if you are a hard core PowerShell scripter and see ways to improve this script, please let me know. 

How to run these scripts

I have only tested this on Windows Server 2008 R2 and PowerShell has its shortcut right on the QuickLaunch. Right-click and run it as administrator. Edit the script so user group reference is correct. If Analysis Services is running on a named instance you will need to modify the connect statement with servername\instancename. Copy your final script and from the top left in the PowerShell window, find the Paste command. After the script is pasted, just press a final Enter and let the script do its work.