CODEWITHSUNDEEP
powershellssas
Cataster
Cataster

Reputation: 3541

How to remove a permission, a member, or a role?

I have this script below to add roles and members and permissions

Import-Module sqlserver
$Server = new-Object Microsoft.AnalysisServices.Tabular.Server
$Server.Connect("SERVER\INSTANCE")

$TabDB = $SERVER.Databases["DATABASENAME"]
$AddRole = new-Object Microsoft.AnalysisServices.Tabular.ModelRole
$AddRole.Name = 'NewRole1'
$AddRole.ModelPermission="Read"
$RoleMember = New-Object Microsoft.AnalysisServices.Tabular.WindowsModelRoleMember
$RoleMember.MemberName = 'DOMAIN\ACCOUNT'
$TabDB.Model.Roles.Add($AddRole)
$AddRole.Members.Add($RoleMember)
$TabDB.Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull)
$server.Disconnect()

How can I remove a permission, a member, and a role?

I tried this at least for the role, but the console returns back "False"

$TabDB.Model.Roles.Remove($AddRole)

Upvotes: 0

Views: 1062

Answers (1)

Jelle Mouissie
Jelle Mouissie

Reputation: 48

When working with ssas from powershell (or C# for that matter) you can use the analysisservices namespace of microsoft: Microsoft analysisservices.

This is an object oriented way of working with ssas databases.

This is an old script I wrote for maintainers: 

function addRoleToDb {
  $roleName = Read-Host "How should this role be called?"
  if ($global:dataBase.Roles.findByName($roleName)) {
    echo "This role already exists"
    return
  } elseif ($roleName -eq "") {
    echo "You can't give an empty name for a role."
    return
  }
  echo "Warning: this role will start out empty and will have to be modified in order to be used. (it wil have read permission)"
  [Microsoft.AnalysisServices.Role] $newRole = New-Object([Microsoft.AnalysisServices.Role])($roleName)
  $global:dataBase.Roles.add($newRole)
  $dbperm = $global:dataBase.DatabasePermissions.add($newRole.ID)
  $dbperm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed
  $global:dataBase.Roles.Update()
  $dbperm.Update()
  return
}

At this point I already had a global variable database. This is a method that would add a role to the database. Deleting the database would work practically the same, you would get the instance of the role with:

role = database.roles.findbyname()

or

role = database.roles.findbyid()

and then 

role.Drop(DropOptions.AlterOrDeleteDependents);

You should check that last line before using it because the alterordeletedependants is something I now use in my c# program, I don't remember if it worked in powershell.

Good luck!

Upvotes: 1

Related Questions