Thread: Login problems after Restore
09-03-04, 17:26 #1Registered User
- Join Date
- Jun 2004
Unanswered: Login problems after Restore
I restored a DB from one SQL server to the other. Before doing the Restore I created a blank DB on the destination server. The destination server already had all the logins that were present on the source server. I also created the same users in the destination DB as the source. Before doing the Restore if I look at the users in the Enterprise manager I can see all the users that I created. However after the Restore the only user I see in the Enterprise manager is 'dbo'! If I login using isql and go the newly restored DB and issue 'sp_helpuser', it shows me all the users that I created.
So why don't I see these users from Enterprise manager?? It looks like an ID mismatch problem between syslogins and sysusers.
Next I login using ISQL to the destination server using one of the corrupted? logins. I try to go to the restored DB and it complains that this is not a valid user. So I come out and login in again as SA and go the restored database and try to add the earlier user. It says user already exists. I'm not sure whats the right way out here. Any help appreciated.
09-03-04, 21:35 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
Issue sp_change_users_login 'report' which will display all unmapped SQL Server Standard Security-based users that need to be fixed. You can either issue sp_change_users_login 'Update_One', <userid>, <login>, or generate a script with the above syntax."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."