Query User Role and Group Membership with SQL

Author: Joyce Zhou

Introduction

In a production environment, User membership can get complicated due to nested User Groups.

Example

The following query gives an overview of the mapping of all Users and User Groups.

SELECT F.UserID
    ,U.UserName
    ,F.ParentID
    ,R.UserName [ParentName]
FROM fnGetAllUsersAffiliations() F
INNER JOIN Users U
ON F.UserID = U.UserID 
INNER JOIN Users R
ON F.ParentID = R.UserID 
WHERE F.UserID <> F.ParentID

Here is a sample result. Please note, this result has unfolded all of the nested User Group relationships.