Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Add SQL Server Analysis Services Role Members via PowerShell or XMLA


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

Problem

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

Solution

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.

xmla

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.

RoleID

DatabaseID

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="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<RoleID>Role</RoleID>
<DatabaseID>MultidimensionalProject1</DatabaseID>
</Object>
<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>Role</ID>
<Name>MyFavoriteRole</Name>
<Description>Role to beat all other roles</Description>
<Members>
<Member>
<Name>test1</Name>
</Member>
<Member>
<Name>test2</Name>
</Member>
</Members>
</Role>
</ObjectDefinition>
</Alter>

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

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

Conclusion

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


Last Update:


next webcast button


next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, May 08, 2018 - 7:02:23 AM - Scott B Murray Back To Top

 

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 07, 2018 - 2:07:25 AM - baatch Back To Top

 Hello,

 

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

sqlserver:

cd \sqlas

cd asazure://westeurope.asazure.windows.net/testserver

 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?

 


Learn more about SQL Server tools