DECLARE @RoleGroup TABLE (Role VARCHAR(20), Grp VARCHAR(20))
INSERT @RoleGroup VALUES
('Role1', 'GrpA'),
('Role1', 'GrpB'),
('Role1', 'GrpC'),
('Role2', 'GrpA'),
('Role2', 'GrpB'),
('Role3', 'GrpA'),
('Role3', 'GrpD')

DECLARE @UserGroup TABLE (Usr VARCHAR(20), Grp VARCHAR(20))
INSERT @UserGroup VALUES

('User1', 'GrpA'),
('User1', 'GrpB'),
('User2', 'GrpA'),
('User2', 'GrpB'),
('User2', 'GrpC'),
('User3', 'GrpA'),
('User3', 'GrpD'),
('User4', 'GrpD')



SELECT 
	Usr, 
	Role
FROM
	(
		SELECT
			*
			,ROW_NUMBER() OVER (PARTITION BY Usr ORDER BY GrpCount DESC, Role DESC) AS RowNum
		FROM
			(
				SELECT 	
					UG.Usr,
					RG.Role,
					COUNT(*) AS GrpCount
				FROM 
					@UserGroup AS UG
				INNER JOIN 
					@RoleGroup AS RG
					ON UG.Grp = RG.Grp
				GROUP BY
					UG.Usr,
					RG.Role
			) AS T
	) AS T1
WHERE
	RowNum = 1
	

SELECT rg.Role, ug.Usr, COUNT(ug.Grp) AS Group_Count
FROM @UserGroup ug
INNER JOIN @RoleGroup rg ON
    rg.Grp = ug.Grp
LEFT OUTER JOIN (
    SELECT Role, COUNT(Grp) AS Grp_Count
    FROM @RoleGroup
    GROUP BY Role
) AS rg_totals ON
    rg_totals.Role = rg.Role
LEFT OUTER JOIN (
    SELECT Usr, COUNT(Grp) AS Grp_Count
    FROM @UserGroup
    GROUP BY Usr
) AS ug_totals ON
    ug_totals.Usr = ug.Usr
GROUP BY
    ug.Usr,
    rg.Role
HAVING
    MAX(rg_totals.Grp_Count) = MAX(ug_totals.Grp_Count) AND
    COUNT(ug.Grp) = MAX(ug_totals.Grp_Count)
ORDER BY Role, Usr

WITH cteSource(Usr, [Role], rn, cnt)
AS (
	SELECT		u.Usr,
			r.[Role],
			ROW_NUMBER() OVER (PARTITION BY Usr ORDER BY COUNT(*) DESC, [Role] DESC) AS rn,
			COUNT(*) OVER (PARTITION BY Usr) AS cnt
	FROM		@UserGroup AS u
	INNER JOIN	@RoleGroup AS r ON r.Grp = u.Grp
	GROUP BY	u.Usr,
			r.[Role]
)
SELECT	Usr,
	[Role]
FROM	cteSource
WHERE	rn = 1
	AND cnt >= 2;