When I copy a database from one server to another via backup and restore, I find I have to recreate the users in the database. I can't login as them right after the copy - I get cannot open default database, when the database that was just copied is the default database. Why is this? The logins exist on the target copy server.
A:
The logins don't actually exist on the target copy server -- it just has logins that happen to have the same name. Internally, all logins are SIDs and since the SID for 'joesmith' on the target server is different from the SID for 'joesmith' on the origin server, they're not considered the same user even though the name is the same.
After you restore on the target server, you need to run 'sp_change_users_login' for the broken users.
pjabbott
2010-03-16 22:22:14