Automate SSAS Syncing Part 1: Create SSAS Database Role


Every day this week I am going to be posting part of a script that I recently wrote, but broken down into separate components that are useful in their own right. The full script, which I will be sharing at the end of the week, automates the synchronisation of SSAS cubes from one instance to another.

So to make this an automated process that was robust to any deviation I had to make sure that the destination service account has full admin access to the source database, that the destination disk could stand the anticipated growth as well as the copy of the sync’d cube, and that the datasource’s connection string on the destination database was reset to it’s original value. Typically the datasource does not need updating, but the destination databases are used in a different environment with their own datasource. And as the sync also syncs the datasource it is necessary to update the datasource back to its original value

The script below will create a role on each database that is on the SSAS Instance. It also gives the role full administration access. Because of this, you don’t actually have to give it read/process rights, but I’ve added them nonetheless and they can be removed.

If you don’t want the script to run for each database you can use the “findbyname” method on the $svr.databases to identify a specific database, but as I want to add the role on each database I’m using a foreach loop.

If the role exists then this script skips creating the role, but checks to see if the role has the administrator permission, and if it does not then it adds it.

Here are the links for the other parts of the series
Automate SSAS Syncing Part 1: Create SSAS Database Role
Automate SSAS Syncing Part 2: Create SSAS Role And Add Member

[string] $ssasInstance,
[string] $rolename)


$svr = new-Object Microsoft.AnalysisServices.Server

foreach ($db in $svr.Databases)
# Print the Database Name
"Database: " + $db.Name
$foundRole = $norole
$newroles = $null
$newroles = $db.Roles.FindByName($rolename)
if ($newroles -eq $null)
"Role not found!"
#let's create the role
#Create Role
$roleToCreate = new-Object([Microsoft.AnalysisServices.Role])($rolename)
“… ‘” + $roleToCreate.Name + “‘ adding to database”

#add the permissions
$dbperm = $db.DatabasePermissions.Add($roleToCreate.ID)
$dbperm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed
$dbperm.Process = [Microsoft.AnalysisServices.ReadAccess]::Allowed
$dbperm.Administer = [Microsoft.AnalysisServices.ReadAccess]::Allowed
else {"role "+$newroles.Name }

#check to see if role that exists has administer permission
 #if not then administer is added
 $dbperm = $db.DatabasePermissions.FindByRole($newroles)
 if ($dbperm.Administer -eq $false)
 $dbperm.Administer = [Microsoft.AnalysisServices.ReadAccess]::Allowed


In tomorrows script I will be adding members to the role just created.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

2 thoughts on “Automate SSAS Syncing Part 1: Create SSAS Database Role”

Leave a Reply

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

You are commenting using your 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