Logins mappings disappear after move/restore database

dakota5
dakota5 used Ask the Experts™
on
On server 2008, when I restore current copies of our production database to a different location (an identical developtment database running on SQL 2008 R2) the users for the database are still there, and so are the logins, but the mapping of the login to the database is gone.
In SQL Server Management Studio, if I try to remap the login for USER1 to the database where he already is listed as a user (but does not appear in the mapping window for the login) I get an error that "USER1 already exists in that database".  Yes he does exist, but because the login is not mapping there, USER1 cannot login to his appropriate database.

I can delete the user from the database (after assigning his schema to someone else), map the login back to the database and give him back his schema.  But all the complex securables for the user are gone.

running sp_change_users_login "auto_fix', 'user1'
found nothing to fix.

Suggestions?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Because your logins on the other server have different SIDs, the logins restored in the database don't match.  These are called "orphaned" logins.  To fix them, do this:

-- See which logins are orphaned:
EXEC  sp_change_users_login  'Report'

-- fix an orphaned user:
EXEC  sp_change_users_login  'Auto_Fix',  'username'   -- where the username is from the report above

Author

Commented:
sp_change_users_login "auto)fix" does work.  You need to be attached to the specific database, not the master.

But still, isn't there a way to avoid this issue, or a better way to fix it?
To avoid the issue, you must first migrate the logins from the original server onto the new server before you restore the database.
You can do that like this (on the original server) :

   exec master..sp_help_revlogin 'myLoginName'

This will generate a little piece of code which you then run on the new server, creating this login on the new server with the same SID it had on the original server.  That way when you restore the database from the original server to the new, the SIDs will match.


My previous post assumes the login doesn't already exist on the new server.  If it does, you should drop it first if you want to try the above.
>> But still, isn't there a way to avoid this issue, or a better way to fix it?  <<
There is a way to avoid this issue. Use the GUI.
jorge, please elaborate.
After using «SQL Server Management Studio» to do the restore database, the logins and the users are still mapped.

You just need to become a bit familiar with the «SQL Server Management Studio».

Start > All Programs > Microsoft SQL Server 2008 > SQL Server Management Studio > ...connect.. > databases > restore database

Please read: How to: Restore a Database Backup (SQL Server Management Studio) at http://msdn.microsoft.com/en-us/library/ms177429.aspx.

I'm familiar with how to restore a database using the GUI, but how does that resolve the issue of orphaned logins?

Author

Commented:
jorge:

I did use the GUI to restore the database.  I see no option to synchronize the users with the logins on the destination server;; also no option to automatically run the equivalent of sp_help_revlogin 'myLoginName' to make the logins on the target database the same as they were on the source database.
I used «overwrite the existent database (WITH REPLACE)» a couple of weeks ago, and I it worked fine.
I just tried again, and receive a error message at logon.

I am deep sorry for that, I can not show it now.

Author

Commented:
Jorge:  OK.  Just wanted to make sure I understood.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial