0

I want to provide admin access rights to an account on SQL Server Analysis Services (SSAS) server through code (C# or SQL script). This is how we do it through SQL Server Management Studio (SSMS).

I want to achieve the same through SQL query or may be C# code if it is possible. I searched a lot but I'm not getting anything very relevant.

I've one administrator user which already has the access on the SSAS server. So, I can fire any SQL or MDX query on the SSAS server which results in adding other user as administrator on the SSAS server.

For initial pointers, if I want to achieve the same in SQL Server instance then it is all about firing a few system-defined stored procedures like sp_addrolemember to grant access to any SQL Server database as detailed here. I'm trying to achieve something similar if at all it is possible through code for an SSAS server.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
RBT
  • 24,161
  • 21
  • 159
  • 240
  • [This](https://stackoverflow.com/questions/31422425/) should get you started, though we could do with a single answer. – Jeroen Mostert Jul 10 '17 at 10:48
  • Well, you found the time to re-edit the tags multiple times. Anyway, it would help to post something on the relevant [meta question](https://meta.stackoverflow.com/questions/356097/limit-access-to-this-tag-burninate-or-merge-with-access-control) before starting the removal. I would just have removed the tag if I had known what you were doing. @MartijnPieters – Andre Jul 05 '20 at 19:12

1 Answers1

0

Here is the batch that adds DOMAIN\user in the Administrators role of an SSAS Instance.

<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'>
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object />
  <ObjectDefinition>
    <Server 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>HOST</ID>
      <Name>SSAS_INSTANCE</Name>
    </Server>
  </ObjectDefinition>
</Alter>
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <RoleID>Administrators</RoleID>
  </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>Administrators</ID>
      <Name>Administrators</Name>
      <Members>
        <Member>
          <Name>DOMAIN\user</Name>
        </Member>
      </Members>
    </Role>
  </ObjectDefinition>
</Alter>
</Batch>
Hybris95
  • 2,286
  • 2
  • 16
  • 33