The function CombineGroupNames() can be optimzed using this concat-logic:
DECLARE @Groups VarChar(MAX)
SELECT @groups = coalesce(@groups +'|' + G.GroupName. G.GroupName)
FROM Resource R
INNER JOIN ResourceSecurityGroup RG
ON R.ResourceID = RG.ResourceID
INNER JOIN SecurityGroup G
ON G.GroupID = RG.GroupID
WHERE R.ResourceID = @ResourceID
RETURN @Groups
/Mike