Using one of Jeff Moden's splitter functions

DECLARE @pString VARCHAR(MAX) = 'BUILTIN\ADMINISTRATORS: [System Admin]YES;[Security Admin];[Server Admin];[setup Admin];[Process Admin];[Disk Admin]YES;[Database Creator];'

DECLARE @pDELIMITER CHAR(1) = ';'

;WITH SPLITS AS (

SELECT ItemNumber = ROW_NUMBER() OVER (ORDER BY t.N),

Item = SUBSTRING(@pString, t.N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, t.N) - t.N)

FROM dbo.Tally as t

WHERE t.N <= DATALENGTH(@pString)+1 --DATATLENGTH allows for trailing space delimiters

AND SUBSTRING(@pDelimiter + @pString, t.N, 1) = @pDelimiter

)

SELECT Item

FROM SPLITS

WHERE ITEM Like '%YES'

ORDER BY ItemNumber

Then you just concatenate the resulting items with a delimiter, using STUFF, as you were originally experimenting with?