1

I am an administrator for a bunch of SSAS Instances.

Sometimes I need to install or migrate SSAS instances to different machines or even different AD domains (we have a couple of different AD domains in a trusted relationship).

Same as with sql server, I need to be able to export and import logins and permissions between instances.

Also, as part of maintenance plan I like to save logins and permissions as well as backup ssas databases.

I would to script the administration logins via script either T-SQL or Powershell so that I can automate it.

at the moment, because I was in a rush I did it in this way:

  1. Connect to the original server as administrator and script all the permissions into a mdx file.
  2. Copy this script and run it on the target server.

enter image description here

How can I script administrator permissions on a SSAS server?

1

I highly recommend you check out Analysis Management Objects (AMO) - I use it to perform all maintenance tasks in SSAS such as creating Roles, Permissions, Databases and Models.

I prefer to use C# but there are plenty of examples online of using the same classes in PowerShell. You need to make sure you use the correct classes based on the type of install that you have (multidimensional or tabular). It looks like you have Tabular in your example above so please refer to the Microsoft.AnalysisServices.Tabular Namespace primarily.

This is how I would perform some tasks in c# using AMO:

// Copy Roles

var sourceServer = new Server();
var targetServer = new Server();

sourceServer.Connect("DEV1"); // replace with IP address, DNS etc...
targetServer.Connect("DEV2");

foreach (Role sourceRole in sourceServer.Roles)
{
    var targetRole = new Role();
    sourceRole.CopyTo(targetRole);
    targetServer.Roles.Add(targetRole);
}

targetServer.Update();

// Backup Database
foreach (Databse db in sourceServer.Databases)
{
    db.Backup($"{db.Name}.abf"); // or whatever file you want to back it up to.
}

You can use c# in maintenance plan script tasks or you can create standalone PowerShell scripts using the same classes. Once you get your head around the object model its pretty easy to do anything that you need.

| improve this answer | |
  • 1
    +1 great answer, I will check all this out! I can see that even to backup ssas is much easier than what I have been doing – Marcello Miorelli Apr 25 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.