未加星标

Add SQL Server Analysis Services Role Members via PowerShell or XMLA

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

By:Scott Murray || 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.


Add SQL Server Analysis Services Role Members via PowerShell or XMLA

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.


Add SQL Server Analysis Services Role Members via PowerShell or XMLA

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


Add SQL Server Analysis Services Role Members via PowerShell or 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.


Add SQL Server Analysis Services Role Members via PowerShell or XMLA

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


Add SQL Server Analysis Services Role Members via PowerShell or XMLA

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.


Add SQL Server Analysis Services Role Members via PowerShell or XMLA
Add SQL Server Analysis Services Role Members via PowerShell or XMLA

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.


Add SQL Server Analysis Services Role Members via PowerShell or XMLA

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


Add SQL Server Analysis Services Role Members via PowerShell or XMLA

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


Add SQL Server Analysis Services Role Members via PowerShell or XMLA

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

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

分页:12
转载请注明
本文标题:Add SQL Server Analysis Services Role Members via PowerShell or XMLA
本站链接:http://www.codesec.net/view/531589.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(144)