User goes missing when restoring a database in MS SQL Server

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 ?

• Status: Open
• Question Views: 1948
• Answer Count: 10
• Vote Up 23 Vote Down

• Answer Accepted: Yes
• Question Category: MS SQL Server

Best Answer by Stella

Go To Solution

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?

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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 ?

About Fredrik

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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?

About Fredrik

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

Best Answer

Best Answer

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.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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?

About Fredrik

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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.

About Fredrik

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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!

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS SQL Server

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.

About edd123

Questions
1

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 08/25/2011
• Question Category: MS SQL Server