CODEWITHSUNDEEP
sql-serverbackuprestore
George Hernando
George Hernando

Reputation: 2650

SQL Server 2008 -- User not able to access restored Database

I'm trying to move a database from one SQL Server database running on one machine to a another machine that is the test server and copy of the original.

On the main machine, I took a backup of database myDB. On the test machine, I deleted the existing older database myDB. On the test machine, I restored the new database myDB.

The data seems to have come across successfully. But I have a problem accessing the database. The owner of all tables is 'user1' and user1 exists with the same login on both DB's.

After trying to access the restored database on the test machine though as user1 there are problems. First it says that the password for user1 isn't correct. After resetting the password, it says the user1 doesn't have a default database set. But it is set already to myDB. It is the same name as the restored db -- myDB.

What have I done wrong in restoring the DB? Do I need to have deleted in addition to the DB the user user1 before attempting to restore the backup? I tried that. How do I handle updating a restore and maintaining user access?

Upvotes: 5

Views: 6180

Answers (2)

user3414230
user3414230

Reputation: 327

To avoid this issue add your user ( the user who is taking backup) as a user of the Backup database, and your user should have (login) access in the new instance.

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135021

after doing the restore do a

ALTER USER user1 WITH LOGIN = user1

inside the restored DB, that will fix the mismapped SID

See also Do you still use sp_change_users_login instead of ALTER USER UserName WITH LOGIN = UserName

Upvotes: 9

Related Questions