I was updating a DNN site today and at the same time was migrating the SQL Server 2000 database to SQL Server 2005. I decided to use the Copy Database Wizard since I had never tried it and it worked great. However, the logins did not get updated properly. I created the login in the SQL Server 2005 security, but could not access the database via the old login. I tried doing a generic detach > attach with the same issue. Trying to edit the SQL Server Account through SQL Server Studio Management Studio would generate an error of "Login must be specified", yet it would not give me the ability to update (all grayed out). After doing some digging, I found the following stored procedure that did the trick:

EXEC sp_change_users_login 'Auto_Fix', 'USERNAME', NULL, 'PASSWORD'

After running the above, I was able to use the old login to access SQL Server. Now back to the grind...