I was recently required to create an automation task that would apply security settings to a all databases in a SSAS instance. The SSAS instance was created from a snapshot where no existing permissions existed on the source.
This therefore required me to perform the following;
1) Create a database role for each database.
2) Add a specific domain security group to the database role membership list.
3) Grant the process database and read definition database permissions to the role.
4) For each cube in the database grant Read/Write and Drillthrough access.
This was all accomplished using a powershell script which loaded the SQL Server Analysis Services assemblies as below;
# Loads SQL Server Analysis Services assemblies.
[System.reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) >$null
# Creates a connection to the SQL Server Analysis Services instance
[Microsoft.AnalysisServices.Server]$Server = New-Object([Microsoft.AnalysisServices.Server])
$Server.Connect(“”) # Specify the SQL Server Analysis Services Instance.
# Loops through each database in the SQL Server Analysis Services Instance.
foreach ($DB in $Server.Databases)
{
# Creates the database role with the client specific security group as a member and adds the database role to the connected datase.
$Role = “” # Specify the name of the role group to create
$Group = “” # Specify the name of the domain security group to add to role group membership list
[Microsoft.AnalysisServices.Role] $Role = new-Object([Microsoft.AnalysisServices.Role])(“$Role”)
$Role.Name
$Role.Members.Add($Group)
$Role.Members.count
$DB.Roles.Add($Role)
$Role.Update()
# Grants process database and read definition database permissions for the ‘Analysts’ role.
$DBPerm= $DB.DatabasePermissions.Add($role.ID)
$DBPerm.Process = [Microsoft.AnalysisServices.ReadAccess]::Allowed # Grants process database permissions for the role.
$DBPerm.Update()
$DBPerm.ReadDefinition = [Microsoft.AnalysisServices.ReadDefinitionAccess]::Allowed # Grants read definition database permissions for the role.
$DBPerm.Update()
# Loops through each cube in the connected database.
foreach ($Cube in $DB.Cubes)
{
# Grants Read/Write access and drillthrough access to each cube for the ‘Analysts’ role.
$CubePerm= $Cube.CubePermissions.Add($Role.ID)
$CubePerm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed # Grants read access to each cube in the database
$CubePerm.Update()
$CubePerm.Write = [Microsoft.AnalysisServices.WriteAccess]::Allowed # Grants write access to each cube in the database. This permission requires read access to apply.
$CubePerm.Update()
$CubePerm.ReadSourceData = [Microsoft.AnalysisServices.ReadSourceDataAccess]::Allowed # Grants drillthrough access for each cube in the database.
$CubePerm.Update()
}
}
# Disconnects the connection to the SQL Server Analysis Services instance
$Server.Disconnect()
Deans Blog 