How to connect an existing SQL Server login to an existing SQL Server database user of same name

The new way (SQL 2008 onwards) is to use ALTER USER

ALTER USER OrphanUser WITH LOGIN = correctedLoginName;

I think it's sp_change_users_login. It's been a little while, but I used to do that when detaching and reattaching databases.