Get list of SSAS server admins

Question

Is there any easy way to programmatically retrieve a list of SSAS Server Admins?

I would love a simple list of:

domain\user1
domain\user2

I can manually get a list in SSMS by connecting to SSAS, right clicking the server and choosing Properties, and then going to the Security page.

I can see them here, but there is no easy way to copy out the list. I can select Script > Script Action to x, but then I have to clean up the output every time for every server. I would rather just run a report. Any options?

score 1 · Answer 1 · answered Jan 08 '22 at 07:32

1

You can run an XMLA query against your SSAS instance as follows:

DISCOVER_XML_METADATA

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
    <RequestType>DISCOVER_XML_METADATA</RequestType>
    <Restrictions>
        <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
            <RoleID>Administrators</RoleID>
            <ObjectExpansion>ExpandObject</ObjectExpansion>
        </RestrictionList>  
    </Restrictions>
    <Properties>
    </Properties>
</Discover>

Sample output in my machine:

answered Jan 08 '22 at 07:32

Subbu

2,130
1
19
28

This still doesn't help much for getting a report across multiple servers. I have to run multiple XMLA queries, copy the output somewhere else, and then remove all the tags to get a simple list of names. I would use DISCOVER_XML_METADATA from Power Query, but it only works from XMLA and not from a SELECT statement. This link looks promising but I've never been able to get msmdpump working: https://stackoverflow.com/questions/65001223/sending-xmla-discover-xml-metadata-to-ssas-from-an-sql-query – TheRizza Jan 10 '22 at 20:33
Your command works great in PowerShell. Working on that now... – TheRizza Jan 10 '22 at 22:38

score 1 · Accepted Answer · answered Jan 11 '22 at 21:14

This is easy to do in PowerShell. Install the SQL Server PowerShell module from an elevated PS prompt with Install-Module -Name SqlServer. See Microsoft link for more info. Then run the script below.

This script uses @Subbu's XMLA query. My PowerShell is weak, so I welcome any suggestions to improve the code.

function Get-Admins($Server) {
    [xml]$Result = Invoke-ASCmd -Server:$Server -Query "<Discover xmlns=""urn:schemas-microsoft-com:xml-analysis""><RequestType>DISCOVER_XML_METADATA</RequestType><Restrictions><RestrictionList xmlns=""urn:schemas-microsoft-com:xml-analysis""><RoleID>Administrators</RoleID><ObjectExpansion>ExpandObject</ObjectExpansion></RestrictionList></Restrictions><Properties></Properties></Discover>"
    Write-Output ""
    Write-Output "$Server Administrators"
    Write-Output '-----------------------------'
    Write-Output $Result.DiscoverResponse.return.root.row.METADATA.Role.Members.Member.Name
}

(get-date).ToString("MMMM d, yyyy")
Get-Admins 'server1'
Get-Admins 'server2'
Get-Admins 'server3'

Output is:

January 11, 2022

server1 Administrators
-----------------------------
domain\user1

server2 Administrators
-----------------------------
domain\user1
domain\user2

server3 Administrators
-----------------------------
domain\user2

I saved the output to a file by doing the following from a PowerShell (PS) prompt:

cd "path to my script directory in quotes"
.\Get-Admins.ps1 > Admins.txt

Get list of SSAS server admins

2 Answers2