Adding SSAS roles via Powershell

I needed to delve into Powershell again recently as a means to automate the creation of a long list of roles and the addition of AD groups to them.

Since there were so many i thought it would be easier to have a list of roles in a text file and just parse that to create all your roles automatically. The script posted here is a first pass that was used during development and does the following:

  • iterate through a text file containing a list of roles.
  • For each line found:
    • check if the role exists and then delete it. (the script caters for an initial role creation so the delete was more for testing)
    • Create a new role object from the current line in the source text
    • Add role to SSAS database
    • Add database permission for role
    • Add cube permissions for role to all cubes in database
    • Associated an AD group and allowed set with the role – my example uses [Organisation].[Cost Centre Code] where the member names are the same as the role names. Another assumption for is that the AD group has the same name as the role. One could extend this script easily to parse a CSV for more complex requirements)
    • log any errors found when adding new objects to the database


I got the basic code from Darren’s post and made a bunch of changes to suit my requirements. So here it is….

# Script to create roles in EIS cube databases via a text file

[System.reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)
[System.reflection.Assembly]::LoadWithPartialName(“System.IO”)

$logFile = “PATH TO LOG FILE”
$sw = new-object System.IO.StreamWriter($logFile)

$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect(“localhost”)

$dbname = “YOUR_SSAS_DB_NAME”
$db = $svr.Databases.item($dbname)

# Print the Database Name
“Adding Roles to Database: ” + $db.Name

#read file here
$roleLines = Get-Content “PATH TO TEXT FILE CONTAINING LIST OF ROLE NAMES”

#repeat for every line in the file
foreach ($roleName in $roleLines)
{
“Processing Role for: ” + $roleName
$groupname = “DOMAIN_NAME\” + $roleName

#check if exists and delete if so
$roleToDelete = $db.Roles.FindByName($roleName)

if ($roleToDelete)
{
“Existing role found… deleting!”
$roleToDelete.Drop(“AlterOrDeleteDependents”)

$error.clear()
if ($error[0])
{
$sw.WriteLine($rolename + “: Error deleting existing : ” + $error[0])
}

}
else
{

#Create Role and add AD group
[Microsoft.AnalysisServices.Role] $roleToCreate = new-Object([Microsoft.AnalysisServices.Role])($rolename)
$roleToCreate.Members.Add($groupname)

#Add to db
“… ‘” + $roleToCreate.Name + “‘ adding to database”
$db.Roles.Add($roleToCreate)

$error.clear()
$roleToCreate.Update()

if ($error[0])
{
$sw.WriteLine($rolename + “: Error Adding to DB: ” + $error[0])
}
else
{

#Create Database permission
“… ‘” + $roleToCreate.Name + “‘ updating database permissions”
$dbperm = $db.DatabasePermissions.Add($roleToCreate.ID)
$dbperm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed

$error.clear()
$dbperm.Update()

if ($error[0])
{
$sw.WriteLine($rolename + “: Error updating DB Perms: ” + $error[0])
}
else
{

# add role to each cube
foreach ($cube in $db.Cubes)
{

#create Cube permission
“… ‘” + $roleToCreate.Name + “‘ adding to permissions in : ‘” + $cube.Name + “‘ cube”
$cubeperm = $cube.CubePermissions.Add($roleToCreate.ID)
$cubePerm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed

$error.clear()
$cubeperm.Update()

if ($error[0])
{
$sw.WriteLine($rolename + “: Error updating Cube Perms: ” + $error[0])
}
}

if(!$error[0])
{

#create dimension permission
“… ‘” + $roleToCreate.Name + “‘ adding to Org Structure Dim permissions”
$dim = $db.Dimensions.FindByName(“Organisation”)
$att = $dim.Attributes.FindByName(“Cost Centre Code”)
$dim.DimensionPermissions.Add($roleToCreate.ID)

$dimperm = $dim.DimensionPermissions.add($roleToCreate.ID)
$dimperm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed

“… ‘” + $roleToCreate.Name + “‘ adding cost centre restriction”
$attPerm = $dimPerm.AttributePermissions.Add($att.ID)
$attPerm.AllowedSet = “{[Organisation].[Cost Centre Code].&[” + $roleName + “]}”

$error.clear()
$dimPerm.Update()

if ($error[0])
{
$sw.WriteLine($rolename + “: Error updating Dimension Perms: ” + $error[0])
}
}

}

}

}

}

#cleanup
$sw.close()
$svr.Disconnect()

Advertisements

One Response to Adding SSAS roles via Powershell

  1. Very nice blog post. I absolutely love this website. Keep it up!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: