Avatar of dakota5
dakota5
Flag for United States of America 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?
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
dakota5

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
knightEknight

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61