Hello!

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

[CmdletBinding()]
param(
[Parameter(Position=0,mandatory=$true)]
[string] $ssasInstance,
[Parameter(Position=1,mandatory=$true)]
[string] $rolename)

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

$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect($ssasInstance)

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”
$db.Roles.Add($roleToCreate)
$roleToCreate.Update()

#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
$dbperm.Update()
}
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
 $dbperm.Update()
 }

}
$svr.Disconnect()

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