asked on

Logins mappings disappear after move/restore database

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?

ASKER CERTIFIED SOLUTION

knightEknight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dakota5

ASKER

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?

SOLUTION

knightEknight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

knightEknight

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.

jorgedeoliveiraborges

>> 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.

knightEknight

jorge, please elaborate.

jorgedeoliveiraborges

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.

knightEknight

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

dakota5

ASKER

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.

jorgedeoliveiraborges

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.

dakota5

ASKER

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