By: Scott Murray | Updated: 2017-02-06 | Comments (2) | Related: > 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.
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.
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.
Now we have a sample script that we can use as a basis for our XMLA work.
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="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.
Otherwise, we would see something similar to the message shown in the following screen print.
Reviewing the role membership in SQL Server Data Tools, we can see the test2 user was added to our WideWorldImportersDW OLAP Cube database.
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.
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.
To continue with our example, I actually created a new Role called "MyFavoriteRole_COPY_V1".
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.
Yes, that is it; now test2 is a member of our role as shown below.
Removing a role follows the same pattern:
remove-rolemember -membername "test2" -database "WideWorldImportersDW" -rolename "MyFavoriteRole_Copy_V1"
Now the test2 users is no longer a member of the role!
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
About the author
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
Article Last Updated: 2017-02-06
