I am setting up a disaster recovery box for the production website. For this, I took a backup of the production MS SQL database and restored it into the DR box.
When I run the application, I get an access denied message. I checked the database and I found that the user mapping is gone!
How do I restore it ?
Can you let me know which version of SQL Server are you using?
We are using Microsoft SQL Server 2005 in the production server. The same version is installed in the DR box. I checked the service packs as well. Both are identical.
What could be the issue ?
Thank you Fredrik, for the version information. When you restore a database backup to a new production box, the "User" mapping in the database, it gets disconnected from the "User Principal" name.Â
User Principal is the SQL server login credential.
This happens if you have a named user in the production box and you do not have the same user set up in the DR box.
You will need to re-map the user via the sp_change_users_login system stored procedure.
Forgive me for asking, but what are the parameters that I need to pass to this system stored procedure?
The stored procedure needs to be called with the following parameters:
sp_change_users_login 'UPDATE_ONE', 'database username' , 'SQL server login user name'
The stored procedure will turn red automatically, if you are typing it inside SQL Server Management Studio.
To be safe, please type the exact login name taking the character case into consideration.
I am using Microsoft SQL Server Management Studio and I can see the query turning red as you have indicated. Next question is should I execute this on the master database or should I execute it under the respective database?
You need to execute it under the respective database, and not the master database.
From the drop down of databases, select your restored database. Or use the "use datbasename" statement before running the query.
Thank you, Stella! It worked! The application works correctly in the DR box.
Glad to be of help! Have a nice day!
