April 15, 2010 at 3:36 pm
Hi
I have a prod db on server 1 with User A and User B. Now it has to be restored on another Server 2 which has the test version of the same prod db with same User A and User B but with different passwords.
Could any one please tell me how to do this restore? I am getting user login problems.
Thank You,
Best Regards,
SQLBuddy
April 15, 2010 at 4:32 pm
After you restore the database, you need to fix the orphaned users. The test server will have different SIDs than the prod database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 15, 2010 at 6:57 pm
Hi Jason,
If we are restoring the db from Server 1 to a database (Server 2 not having the logins, Login A and Login B with different passwords) on Server 2, then we can fix this issue with sp_help_revlogin i.e by creating the logins from server 1 using sp_help_revlogin on the server 2 and then mapping them to the users in the restored database.
If the instance on server 2 already has the same logins, Login A and Login B but different passwords, then how to resolve this? In such a case we can't create logins using sp_help_revlogin procedure and map them to the users in the restored database.
Thank You,
Best Regards,
SQL Buddy
April 15, 2010 at 8:03 pm
You use the procedure sp_change_users_login with the auto login parameter and identify the login/user to be fixed.
Look up the procedure in Books Online for exact syntax and available parameters.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 15, 2010 at 9:20 pm
In this scenario you do not want to use sp_help_revlogin. That would recreate the the prod user with the prod password if the login did not exist on the test server. You need to fix the orphan users. That is done through the proc that Jeff mentioned.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 15, 2010 at 9:56 pm
Hi Jeff and Jason,
Thank you very much for your great help. I will check that.
Thank You,
Best regards,
SQLBuddy
April 15, 2010 at 11:23 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply