SQL Query to get Group Members

I didn’t see the posted anywhere.  If you know of someone with a script like this, please put it in the comments. 

Boris has a nice PS script to do this here.

To get a quick list of the objects in all groups, the following SQL query works

Select BMESource.FullName as [Group], BMETarget.DisplayName as [Member]
  From BaseManagedEntity BMESource
    Inner Join Relationship R
      On R.SourceEntityId = BMESource.BaseManagedEntityId
    Inner Join BaseManagedEntity BMETarget
      On R.TargetEntityId = BMETarget.BaseManagedEntityId
  Order by BMESource.FullName

Note that this applies to all objects that are the source of any containment relationship.  If you just want groups, you can add the where clause

Select BMESource.FullName as [Group], BMETarget.DisplayName as [Member]
  From BaseManagedEntity BMESource
    Inner Join Relationship R
      On R.SourceEntityId = BMESource.BaseManagedEntityId
    Inner Join BaseManagedEntity BMETarget
      On R.TargetEntityId = BMETarget.BaseManagedEntityId
    Inner Join ManagedEntity MT1
      on BMESource.BaseManagedEntityId = MT1.ManagedEntityId
    Inner Join ManagedEntity MT2
      on MT1.BaseManagedEntityId = MT2.ManagedEntityId
    Where MT2.TypeName = ‘System.Group’
  Order by BMESource.FullName

Obviously, you can add other needed fields, such as ID in the initial select

Select BMESource.FullName as [Group], BMESource.BaseManagedEntityId as [Group ID], BMETarget.DisplayName as [Member], BMETarget.BaseManagedEntityId as [Member ID]
From…

This is a hack job, but I need to run.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to SQL Query to get Group Members

  1. Jonathan says:

    Hi Mike. I stumbled across your blog tonight. Looks like some good information here. I do have a SQL query posted on my blog, in case you haven\’t found your solution yet.http://blogs.technet.com/jonathanalmquist/archive/2009/04/25/all-groups.aspx-Jonathan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s