List of analysis server users from either sql or mdx

_{Monday, May 17, 2010 9:38 AM}

Good Morning

Can one get a list of analysis servers users from a sql or mdx query?

thanks

David Hills

_{Tuesday, May 18, 2010 3:22 AM ✅Answered}

For SSAS 2005 you could use the DMV function from http://asstoredprocedures.codeplex.com to get the equivalent of a SSAS 2008 DMV by running something like the following:

call ASSP.DMV('Select * from $system.discover_connections')

But as you already mentioned, this will only give you the currently connected users.

To list all the possible users, something like the new Roles Report (http://bidshelper.codeplex.com/wikipage?title=Roles%20Report&referringTitle=Home) in BIDSHelper http://bidshelper.codeplex.com might be what you are after as it will even recursively expand groups to show the users in those groups.

http://geekswithblogs.net/darrengosbell - please mark correct answers

_{Monday, May 17, 2010 9:50 AM}

Hi David,

You can , by using information from the AS Schema Rowsets.

For instance:

Open SSMS and Create New MDX query and use this command:

select distinct connection_user_name from $system.discover_connections

It'll show you the active AS users.

HTH,

Cees

Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.

_{Monday, May 17, 2010 3:37 PM}

thanks for your reply Cees

a couple of things

1)I'm on AS2005 and discover_connections seems to be AS 2008 only

2)More significantly I'm looking for users that have permission to access the cube, not  that have a current connection, and I guess the $system.discover_connections would show who is connected rather than who can connect.

got any other idears

Thanks again

David

_{Monday, May 17, 2010 5:01 PM}

Hi David,

I don't have a script taking care of this, so the only thing I can think of right now is to script the roles one-by-one. For a specific role, this results in:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ParentObject>
    <DatabaseID>Analysis Services Project2</DatabaseID>
  </ParentObject>
  <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">
      <ID>Role</ID>
      <Name>Sales</Name>
      <Members>
        <Member>
          <Name>CNL08850\Guest</Name>
          <Sid>S-1-5-21-1662486593-1936731478-285943990-501</Sid>
        </Member>
        <Member>
          <Name>CNL08850\cvd</Name>
          <Sid>S-1-5-21-1662486593-1936731478-285943990-1024</Sid>
        </Member>
      </Members>
    </Role>
  </ObjectDefinition>
</Create>

The member name fields refer to AD accounts with access to the cube.

Hope somenon else can provide us with someting better than this.

r,

Cees

Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.