User goes missing when restoring a database in MS SQL Server

Asked By 230 points N/A Posted on -
qa-featured

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 ?

SHARE
Best Answer by Stella
Answered By 0 points N/A #97994

User goes missing when restoring a database in MS SQL Server

qa-featured

Can you let me know which version of SQL Server are you using?

Answered By 230 points N/A #97995

User goes missing when restoring a database in MS SQL Server

qa-featured

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 0 points N/A #97996

User goes missing when restoring a database in MS SQL Server

qa-featured

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 230 points N/A #97997

User goes missing when restoring a database in MS SQL Server

qa-featured

Forgive me for asking, but what are the parameters that I need to pass to this system stored procedure?

Best Answer
Best Answer
Answered By 0 points N/A #97998

User goes missing when restoring a database in MS SQL Server

qa-featured

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 230 points N/A #97999

User goes missing when restoring a database in MS SQL Server

qa-featured

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 0 points N/A #98000

User goes missing when restoring a database in MS SQL Server

qa-featured

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 230 points N/A #98001

User goes missing when restoring a database in MS SQL Server

qa-featured

Thank you, Stella! It worked! The application works correctly in the DR box.

Answered By 0 points N/A #98002

User goes missing when restoring a database in MS SQL Server

qa-featured

Glad to be of help! Have a nice day!

Answered By 0 points N/A #98003

User goes missing when restoring a database in MS SQL Server

qa-featured
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.

Login/Register to Answer

Related Questions