How to index a many to many table most effectively
a clustered index on (userid, roleid)
another index on (roleid, userid)


You don't need a surrogate key (unless you use a braindead ORM) and you almost always need the 2nd index