Sql-server – How to script administrator permissions on a SSAS server

automationsql serversql-server-2016ssasssas-2016

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?

Best Answer

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.

Related Question