getoffmyfoot (2/7/2011)
Such a bummer to me that the sp_change_users_login option is going to be phased out without a "decent" alternative - consider the scenario that exists in my environment where there lots of sql server logins, and (esp. in dev), you don't document the passwords for them, which makes the ALTER USER solution difficult to pull off. Plus, using sp_change_users_login 'Report' lets you see specifically which users are orphaned before you dive into trying to fix them.
You don't need the password of the login in order to use ALTER USER. But I agree the report option is pretty handy. Without that you'd need to run the actual SQL statement that sp_change_users_login uses:
select UserName = name, UserSID = sid from sysusers
where issqluser = 1
and (sid is not null and sid <> 0x0)
and (len(sid) <= 16)
and suser_sname(sid) is null
order by name