I'd be thinking something simple like
Select RoleID from Roles where UserID = @Username
Note - I haven't validated the user against the Users table - I am asuming that was done when the role was added. But if we wanted to then I would go with this
Select RoleID from Roles R, Users U
where R.UserID = U.UserID
and UserID = @Username