How will this only return Admin and Dev, if networking is 'y' in your test data? The returned data should be Admin, Dev, and networking.. correct?

I agree with Andras that table redesign is the best and most effiecient option. If you must use the current design, you can do something like this.

SELECT UserId

FROM users a INNER JOIN roles b ON a.roles LIKE '%' + b.roleid + '%'

WHERE b.Inboxenabled ='y'

GROUP BY UserId

HAVING COUNT(b.roleid) =

(SELECT COUNT(RoleId)

FROM @roles

WHERE Inboxenabled ='y')