Query User Role and Group Membership with SQL

Author: Joyce Zhou

In a production environment, user membership can get complicated due to nested User Groups. We can organize the data by adding a new function, which creates an overview of Users, User Groups, and both of their mappings.

You can add said function with the following script, in SQL Server Management Studio:

CREATE FUNCTION [dbo].[fnGetAllUsersAffiliations] 
(
)
RETURNS @ret TABLE 
(
UserID bigint NOT NULL,
ParentID bigint NOT NULL
)

AS
begin
WITH Ancestors([ID],[ParentID],[Sequence],[UserID0],[Path]) AS 
(

SELECT 
[ID] = ur.UserID,
[ParentID] = ua.Affiliation,
[Sequence] = 0,
uu.[UserID],
[Path] = cast(N'/' as nvarchar(4000)) + cast(ur.UserID as nvarchar(4000)) + cast(N'/' as nvarchar(4000))
FROM dbo.Users ur
inner join dbo.UserAffiliations ua
on ur.UserID=ua.UserID and ur.Deleted <> 1
inner join dbo.Users uu on ur.UserID = uu.UserID
where (uu.[Type]=dbo.fnConstants('Users','regular') or uu.[Type] = dbo.fnConstants('Users','custom'))


UNION ALL


SELECT 
[ID] = ur.UserID,
[ParentID] = ua.Affiliation,
[Sequence] = [Sequence] + 1,
CTE.[UserID0],
[Path] = CTE.[Path] + cast(ur.UserID as nvarchar(4000)) + cast(N'/' as nvarchar(4000))
FROM Ancestors AS CTE, 
dbo.Users ur
inner join dbo.UserAffiliations ua
on ur.UserID=ua.UserID and ur.Deleted <> 1
WHERE CTE.[ParentID] = ur.UserID
and (CTE.[Path] not like cast(N'%/' as nvarchar(4000)) + cast(ur.UserID as nvarchar(4000)) + cast(N'/%' as nvarchar(4000)))
)

INSERT @ret
SELECT distinct [UserID0], ParentID FROM Ancestors
where [UserID0] <> [ParentID]

INSERT @ret
SELECT distinct UserID, UserID FROM dbo.Users where 
([Type]=dbo.fnConstants('Users','regular') or [Type] = dbo.fnConstants('Users','custom'))
and Deleted <> 1

return

end

With the function applied, run the following query to view the Users, User Groups, and their mapping:

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:

Note: This result has unfolded all of the nested User Group relationships.