SQL Server 2008——用户无法访问恢复的数据库
我正在尝试将数据库从一台计算机上运行的 SQL Server 数据库移动到另一台计算机，该计算机是测试服务器和原始服务器的副本。
在主机上，我备份了数据库 myDB。 在测试机上，我删除了现有的旧数据库 myDB。 在测试机上，我恢复了新的数据库myDB。
数据似乎已经成功找到。 但我在访问数据库时遇到问题。 所有表的所有者都是“user1”，并且 user1 在两个数据库上以相同的登录名存在。
在尝试以 user1 身份访问测试计算机上恢复的数据库后，出现问题。 首先它说 user1 的密码不正确。 重置密码后，提示user1没有默认数据库设置。但它已经设置为 myDB。它与恢复的数据库同名——myDB。
我在恢复数据库时做错了什么？ 在尝试恢复备份之前，除了数据库之外，我是否还需要删除用户 user1？我试过了。如何处理更新恢复和维护用户访问权限？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
执行还原后，
在还原的数据库中执行操作，这将修复错误映射的 SID
另请参阅您是否仍然使用 sp_change_users_login 而不是 ALTER USER UserName WITH LOGIN = UserName
after doing the restore do a
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
为了避免此问题，将您的用户（正在进行备份的用户）添加为备份数据库的用户，并且您的用户应该在新实例中具有（登录）访问权限。
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.