Home > IT Answers > SQL Server > SQL Server Security Login does not work after restoring database on another computer
Rick Martinez
585 pts.
 SQL Server Security Login does not work after restoring database on another computer
SQL Server, SQL Server security, SQL Server Security Login
I backup the production database on server named SQL1 then restored the database on server named SQL2. SQL server security login name "user1" has been created on both SQL1 and SQL2 servers. When I restore the database on SQL2 I have to re-map and give db_datareader access to the "user1" user name again. How can I fix the problem of having to re-map and give db_datareader access everytime I restore the database on a second server?

Software/Hardware used:
ASKED: July 20, 2009  8:59 PM
UPDATED: July 21, 2009  6:21 PM
RELATED QUESTIONS
Answer Wiki:
There are two ways to fix this. #1 drop the user on the second server, and recreate it using the same SID as the first server has. #2 After you restore the database use the <a href="http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-how-do-i-use-sp_change_users_login/">sp_change_users_login</a> system stored procedure to match up the logins with the users.
Last Wiki Answer Submitted:  July 20, 2009  9:43 pm  by  Denny Cherry   64,550 pts.
All Answer Wiki Contributors:  Denny Cherry   64,550 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Thanks Mr.Denny that solved our problem.

Rick Martinez
 585 pts.