Add SQL Server Analysis Services Role Members via PowerShell or XMLA

By:   |   Comments (2)   |   Related: > Analysis Services Security


How can you add a user (member) to a SQL Server Analysis Services (SSAS) Database role via PowerShell or XMLA scripts?


Working with scripts in SQL Server Analysis Services (SSAS) always requires a different thought process than regular T-SQL scripting. Most of us are familiar with various techniques we can utilize to add users / logins to a SQL Server database. Plus, there are plenty of tips, such as Daniel Calbimonte's Adding an Active Directory User with the Integration Services (SSIS) Script Task or Ahmad Yaseen's Grant User Access to All SQL Server Databases, that provide you with those details.

However, what about SSAS users? The SSAS permissions process centers around the concept of granting permissions to roles; individual members or groups (local or Active Directory) are then added to the roles (see Configuring permissions for SQL Server Analysis Services ). To address this need, in this tip we will cover two scripting methods for getting those users / members added to a role. One method is to use a XMLA script to alter the existing role; the second method uses a PowerShell cmdlet to add a new member.

Add Role Member via XMLA Script

The first step to using XMLA to add a member to a role is to connect to SSAS in Management Studio; some of you may not realize that this connection method was even possible, but it is.

ssas connect

Once we are connected, our window will look similar to the below screen print. On the left side, we have our Object Explorer. In the middle column of the screen, you can see we are connected the WideWorldImportersDW OLAP cube database. Additionally, my default analysis service query window opens to an MDX query type, which is shown on the right side.

ssms XMLA

We will need to change the query type to XMLA in order to execute our XMLA script, as illustrated in the below figure.


We can see the basic Alter script format by right clicking on our script and selecting Script Role as > Alter To > New Query Editor Window.

script role

Now we have a sample script that we can use as a basis for our XMLA work.

Script Role Text

A few points I will mention about the above script (full script is included later in the tip). The RoleID ( both in the top section just after the Object tag and again after the xmlns:xsd closing ) is the Unique ID assigned to the Role. This can be found by reviewing the properties box for the role in SQL Server Data Tools ( aka the Visual Studio IDE for BI ). The DatabaseID can actually be found by reviewing the SSAS database properties in Management Studio. There are other methods of getting at these ID properties, of course, but these are the easiest GUI methods. Furthermore, once you have the IDS, they will not change.

The next tag is the Role tag ObjectDefinition links to the schema definitions for SSAS XMLS script (these definitions define what is in the script). Then, the Role ID, Name, and Description are listed. Finally, the member schema and tags are listed. This section is where we will add additional users.



Currently, we have one user, test1; we will add a second local user, test2. These users could, of course, also be AD users. One other item about the script, I removed the SID line as they will be different for each user.

<Alter ObjectExpansion="ExpandFull" xmlns="">
<Role xmlns:xsd="" xmlns:xsi="" xmlns:ddl2="" xmlns:ddl2_2="" xmlns:ddl100_100="" xmlns:ddl200="" xmlns:ddl200_200="" xmlns:ddl300="" xmlns:ddl300_300="" xmlns:ddl400="" xmlns:ddl400_400="" xmlns:ddl500="" xmlns:ddl500_500="">
<Description>Role to beat all other roles</Description>

Although not blatantly obvious from the results text illustrated below, running the above code is executed successfully. The text in the red box means the code completed without error.

Query Success

Otherwise, we would see something similar to the message shown in the following screen print.

xmla error

Reviewing the role membership in SQL Server Data Tools, we can see the test2 user was added to our WideWorldImportersDW OLAP Cube database.

Role Members in SSDT

We can remove users just as easily by removing its member section in the XMLA script; for example, we could remove <Member> <Name>test2</Name></Member> and execute the alter statement again. Removing these lines and running the statement again would leave just user test1 in the role membership. As you can see, this method is a "full replacement" methodology as opposed to an add methodology. To the contrary, the "add" method will be used in the PowerShell section outlined next.

Add Role Member via PowerShell Script

Adding a member to a role via PowerShell is a second and also very "scriptable" way to add users to your SSAS roles. For SQL 2016, the appropriate SQL Server modules should be added as long as you are using a machine where SQL Server Management Studio 2016 (SSMS) has been installed. In particular the SQLPS module and related sub-modules / sub-components are added during the SSMS install; furthermore, the SSAS SQLASCMDLETS sub module is part of the install and includes the needed commands for adding new role members and removing existing members. We will be using the following CMDLETS:

  • Add-RoleMember
  • Remove-RoleMember

We will first start PowerShell as noted below.

Start PowerShell


Next we execute three PowerShell Commands to get ready to add our new member:

  • Import-Module SQLPS -DisableNameChecking -- this command loads the SQL Server module into our current session
  • sqlserver: -- sets the session to the SQLServer module
  • cd \sqlas\localhost\default -- changes default hierarchy to the SQLAS sub module for the localhost machine and finally to the default hierarchy path.

We will need to navigate to our WideWorldImportersDW OLAP database as we add members.

setu ps

To continue with our example, I actually created a new Role called "MyFavoriteRole_COPY_V1".

copied role

We will now use PowerShell's add-rolemember cmdlet to add test2 to this role. The command is as follows:

add-role member -membername "test2" -database "WideWorldImportersDW" -rolename "MyFavoriteRole_Copy_V1"

and is executed in the screen print below.

add role success

Yes, that is it; now test2 is a member of our role as shown below.

PS Add

Removing a role follows the same pattern:

remove-rolemember -membername "test2" -database "WideWorldImportersDW" -rolename "MyFavoriteRole_Copy_V1"

remove role

Now the test2 users is no longer a member of the role!

remove role result


As you can see from this tip, we have a couple of different avenues to alter and add to the membership of a SSAS roles. Roles control the permissions that are applied to a SSAS database and cube, so adjusting the membership of these roles can be an administrative hassle. One option is to use a XMLA based script to "fully replace" the role membership; the second option is to use the add-rolemember and remove-rolemember PowerShell cmdlets to add or remove users from a role. In both cases, we are provided with a non-GUI, scripting based method of administering our SSAS role membership.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, May 8, 2018 - 7:02:23 AM - Scott B Murray Back To Top (75893)


baatch... that is more than could be provided in a comment.  I will put that in my queue for a future tip if possible.

Monday, May 7, 2018 - 2:07:25 AM - baatch Back To Top (75880)



I'm trying to follow along with the Powershell example and connect to an Azure Analysis Services.

My code looks like this:


Import-module SqlServer


cd \sqlas

cd asazure://

 But I get error message saying: "cd : Cannot find drive. A drive with the name 'asazure' does not exist."


Can you provide an working example with Azure Analysis Services?


get free sql tips
agree to terms