Get list of SSAS server admins

user_3516026 0
Viewed 178

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. enter image description here

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?

ssas-tabular ssas powershell
2 Answers

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:

enter image description here

user_16892260

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
user_35160260
Related