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.