How can I script administrator permissions on a SSAS server?

Question

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.

How can I script administrator permissions on a SSAS server?

Answer 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.

Answer 2

Another way if you are using a stored proc on SQL server:

To create admin permissions on database level, I programmatically created the following XMLA statement and executed it on SSAS from SQL via a linked server (EXEC (@XMLA) AT LINKED_SSAS):

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ParentObject>
        <DatabaseID>Your_SSAS_DB_Name</DatabaseID>
    </ParentObject>
    <ObjectDefinition>
        <Role xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
            <ID>Admin</ID>
            <Name>Admin</Name>
            <Members>
                <Member>
                    <Name>domain\user1</Name>
                    <Sid>user1_SID</Sid>
                </Member>
                <Member>
                    <Name>domain\user2</Name>
                    <Sid>user2_SID</Sid>
                </Member>
            </Members>
        </Role>
    </ObjectDefinition>
</Create>
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
        <DatabaseID>Your_SSAS_DB_Name</DatabaseID>
        <DatabasePermissionID>DatabasePermission 1</DatabasePermissionID>
    </Object>
    <ObjectDefinition>
        <DatabasePermission xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
            <ID>DatabasePermission 1</ID>
            <Name>DatabasePermission 1</Name>
            <RoleID>Admin</RoleID>
            <Process>true</Process>
            <ReadDefinition>Allowed</ReadDefinition>
            <Read>Allowed</Read>
            <Administer>true</Administer>
        </DatabasePermission>
    </ObjectDefinition>
</Alter>