This is a good example of bad table design. You should not store the role membership in a single column as a list. Is this something you could change?
If not, then you could create a user defined function to check membership condition, you could use a condition like: Roles + ',' LIKE '%'+@therolename+',%', or a table valued function that returns a row for each of the role in a string, and use crossapply. The second of the above is the one that is the easiest to add quickly, but the best could be to fix your design.
Regards,
Andras