Asked By
Fredrik
230 points
N/A
Posted on - 05/16/2011
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 ?
Answered By
Stella
0 points
N/A
#97994
User goes missing when restoring a database in MS SQL Server
Can you let me know which version of SQL Server are you using?
Answered By
Fredrik
230 points
N/A
#97995
User goes missing when restoring a database in MS SQL Server
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 ?
Answered By
Stella
0 points
N/A
#97996
User goes missing when restoring a database in MS SQL Server
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.
Answered By
Fredrik
230 points
N/A
#97997
User goes missing when restoring a database in MS SQL Server
Forgive me for asking, but what are the parameters that I need to pass to this system stored procedure?
Answered By
Stella
0 points
N/A
#97998
User goes missing when restoring a database in MS SQL Server
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.
Answered By
Fredrik
230 points
N/A
#97999
User goes missing when restoring a database in MS SQL Server
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?
Answered By
Stella
0 points
N/A
#98000
User goes missing when restoring a database in MS SQL Server
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.
Answered By
Fredrik
230 points
N/A
#98001
User goes missing when restoring a database in MS SQL Server
Thank you, Stella! It worked! The application works correctly in the DR box.
Answered By
Stella
0 points
N/A
#98002
User goes missing when restoring a database in MS SQL Server
Glad to be of help! Have a nice day!
Answered By
edd123
0 points
N/A
#98003
User goes missing when restoring a database in MS SQL Server
If you are willing to restore a username, follow the steps below:
Â
1. On the MASTER or SOURCE SERVER, run sp_help_revlogins and get the script for creating logins.
Â
2. While restoring the DESTINATION SERVER with the backup, run the script for creating logins against the MASTER database on the DESTINATION SERVER.
Â
3. After the restore has been successfully completed, run sp_change_users_logins against the RESTORED database. You can do it another way. If you know the usernames, you will have to find the ones that are missing.
Â
1. EXEC sp_change_users_login ‘Report’
2. Create and restore the SQL user account after you create the missing user accounts: EXEC sp_change_users_login, ‘username’, NULL, ‘password’
Â
1. To create the SQL user account (Note: In the next step you will have to restore the SQL user): CREATE LOGIN ‘username’ WITH PASSWORD= ‘password’
Â
2. In order to restore the SQL user account: EXEC sp_change_users_login ‘Update_One’, ‘Username’, ‘NewUsername’ Now that the sp_change_users_login has been deprecated, you will need to use the WITH LOGIN clause of the ALTER USER command.
