SSAS XMLA DISCOVER: how to get list of roles in SSAS database

Question

I am trying to get a list of roles existing in an SSAS DB using XMLA. I need only roles, not other information.

I know there is a <RequestType>DISCOVER_XML_METADATA</RequestType>:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">  
   <RequestType>DISCOVER_XML_METADATA</RequestType>  
   <Restrictions>
       <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
           <DatabaseID>SSAS_DB_ID</DatabaseID>
       </RestrictionList>
   </Restrictions>  
   <Properties>  
      <PropertyList>
      </PropertyList>  
   </Properties>  
</Discover>

The query returns a ~9Mb XML containing complete information about the database, including the information that I do not need.

Yes, it does contain the piece with the roles:

...
     <Roles>
        <Role>
          <Name>ROLE_NAME_AND_ID1</Name>
          <ID>ROLE_NAME_AND_ID1</ID>
          <CreatedTimestamp>2020-05-13T11:20:27.343333</CreatedTimestamp>
          <LastSchemaUpdate>2020-06-03T06:16:53.816667</LastSchemaUpdate>
          <Description />
          <Members>
            <Member>
              <Sid>SID1</Sid>
              <Name>DOMAIN\LOGIN1</Name>
            </Member>
            <Member>
              <Sid>SID2</Sid>
              <Name>DOMAIN\LOGIN2</Name>
            </Member>
          </Members>
        </Role>
      </Roles>
...

But I would like to have only the role list without anything else.

Could not find a clear explanation on the web or MS website (where the documentation is extremely quirky for me) and would appreciate any insights.

score 1 · Answer 1 · answered Jun 10 '20 at 14:41

Update: I needed to use the list of roles and members in a SQL procedure. I managed to use the result of DISCOVER_XML_METADATA in a procedure. Below is working code.

Still, it has to load the complete XML_METADATA from SSAS (instead of just roles, as I intended in the main question), but it works.

declare @SSASDBName nvarchar(255) = 'SSAS_DB_NAME'

-- when executed from SQL, XMLA Discover has to be wrapped in <Batch>
declare @XMLA nvarchar(MAX)
select @XMLA = '<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">  
   <RequestType>DISCOVER_XML_METADATA</RequestType>  
   <Restrictions>
       <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
           <DatabaseID>'+@SSASDBName+'</DatabaseID>
       </RestrictionList>
   </Restrictions>
   <Properties/>
</Discover></Batch>'

-- get the XML response from SSAS
declare @t table (x XML)
insert @t
EXEC(@XMLA) AT LINKED_SSAS_SERVER -- linked SSAS server

declare @XMLAResult xml
select @XMLAResult = x from @t

-- expand XML response
;with XMLNAMESPACES (default 'urn:schemas-microsoft-com:xml-analysis:rowset')
select
    SSAS_DB                     = X_DB.x.query('./ID').value('.','nvarchar(255)'),
    RoleID                      = X_Roles.x.query('./ID').value('.','nvarchar(255)'),
    RoleMember                  = X_Members.x.query('.').value('.','nvarchar(255)')
from @XMLAResult.nodes('//Database') X_DB(x)
cross apply @XMLAResult.nodes('//Roles/Role') X_Roles(x)
cross apply X_Roles.x.nodes('./Members/Member/Name') X_Members(x)

screenshot

score 1 · Answer 2 · answered Jan 18 '21 at 03:17

Another option is to use the DMVs $SYSTEM.TMSCHEMA_ROLES & $SYSTEM.TMSCHEMA_ROLE_MEMBERSHIPS. You can query using Invoke-ASCmd

e.g.

$serverUri="server.asazure.windows.net/model"

Invoke-ASCmd -Server $serverUri -Query "select * from `$SYSTEM.TMSCHEMA_ROLES"

score 0 · Answer 3 · answered Jun 10 '20 at 12:28

0

One option is to add this free open-source ASSP assembly to your server or database and then run the following (see this example and other role related ones here):

CALL ASSP.DiscoverXmlMetadata("\Database\Roles\Role");

Another option is to use this free open-source Visual Studio extension BI Developer Extensions which can generate a roles report which you can export to PDF. The advantage of this option is it queries Active Directory to recursively expand groups to see the members.

answered Jun 10 '20 at 12:28

GregGalloway

11,355
3
16
47

Thanks, I have seen ASSP mentioned quite a few times. The problem is I cannot install additional assemblies on SSAS server. Also, I have not mentioned that I need to call the XMLA from SQL procedure. Will update my question now. – Igor Zhilin Jun 10 '20 at 14:13

SSAS XMLA DISCOVER: how to get list of roles in SSAS database

3 Answers3

Linked