troubleshooting Question

Logins mappings disappear after move/restore database

Avatar of dakota5
dakota5Flag for United States of America asked on
Microsoft SQL Server 2008
11 Comments2 Solutions1290 ViewsLast Modified:
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?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros