Identify User Membership in SQL Server
By Joyce Zhou - Updated June 5, 2017
In production environment, users membership can get complicated due to nested user groups. The following query gives an overview of the mapping of all users and user groups.
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 the nested user group relationship.