Steps To Resolve Orphaned Users 1. Run the following command for the orphaned user from the preceding step:
Use Northwind go sp_change_users_login ‘update_one’, ‘test’, ‘test’
This relinks the server login “test” with the the Northwind database user “test”. The sp_change_users_login stored procedure can also perform an update of all orphaned users with the “auto_fix” parameter but this is not recommended because SQL Server attempts to match logins and users by name. For most cases this works; however, if the wrong login is associated with a user, a user may have incorrect permissions. 2. After you run the code in the preceding step, the user can access the database. The user may then alter the password with the sp_password stored procedure:
Use master go sp_password NULL, ‘ok’, ‘test’
This stored procedure cannot be used for Microsoft Windows NT security accounts. Users connecting to a SQL Server server through their Windows NT network account are authenticated by Windows NT; therefore, their passwords can only be changed in Windows NT.
Only members of the sysadmin role can change the password for another user’s login.
http://support.microsoft.com/kb/274188/