I agree with using the SELECT ... FOR XML PATH('') to get a set based operation. Not only does it remove the cursor, but it greatly simplifies the code.

What I would recommend doing for it though is to instead of using all of the replace statements, is to:

declare @Groups varchar(max)

select @Groups = (

select '|' + 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

FOR XML PATH(''))

set @Groups = substring(@Groups, 2, 1024)

return @Groups