Adding SSAS roles via Powershell

September 6, 2010

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()


SQL Service Account permission considerations

December 13, 2009

Recently ive had to do a lot of work concerning the setup and configuration of service accounts for SQL 2005 and 2008. An interesting issue surfaced when trying to get SSIS to connect to an Oracle server using the Microsoft OLEDB provider for Oracle (MSDORA.1).

Our DEV and TEST environments were working fine, but I kept on getting the “ORA-12170: TNS: Connect timeout occurred” error in the PROD environment. The error indicated that the Oracle client was successfully installed and that SSIS was able to talk to it but that something else was wrong. After a few days were spent checking logs, firewalls, re-installing the Oracle clients etc we stumbled upon a breakthrough – When an administrator account was used to execute the package in PROD it worked fine!

The service account we had setup as a credential/proxy to run the job step had all the right permissions as far as sql server was concerned.. however the issue proved to be that the service account had no rights on the machine itself. Adding the account as a user on the machine running the SSIS package solved the issue.