How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008?

After you move a database from the instance of one SQL Server to another one, the users may not be able to log in to the database on new servers. Typically users receive the following error message:

Login failed for user '[User Name]'. (Microsoft SQL Server, Error: 18456)

This is because the server level logins were not transferred to the new instance. Please follow the instructions provided at http://support.microsoft.com/kb/918992 for the best resolution of this situation.

However you may have to refer to http://blog.consultdba.com/2010/06/syncing-login-between-database-and.html to sync the logins between the database and server to resolve all the related issues after your migration. (This will be needed when new servers already have the login you are trying to transfer using sp_help_revlogin with a different SID. In that case, new server’s login’s SID and recently migrated databases user’s SID will not match. This recommended procedure will resolve this particular situation)

Best Practice: I will recommend to run the procedures mentioned at http://blog.consultdba.com/2010/06/syncing-login-between-database-and.html after the folllowing the procedures of http://support.microsoft.com/kb/918992. (Unless new server is a brand new one which does not have any login). Running procedures from http://blog.consultdba.com/2010/06/syncing-login-between-database-and.html will not harm anything but make sure that all the logins of the database and server are properly synced.