做网站我们更专业,服务我们更用心----厦门优艾网络工作室
建站热线:135~1596~4500

MSSQL备份移植到另一服务器还原时容易遇到的问题

2/7/2006 2:16:14 PM 来源: 作者: 浏览热度( 次) 字号:T|T
分享到:

MSSQL备份移植到另一服务器还原时容易遇到的问题,尤其是从虚拟主机备份回来的数据库在本机还原的问题…

  1. 会出现用SQL原来的用户名和密码无效的情况
  2. 无法删除某一个系统表
  3. 用sa连接做SELECT时提示表名无效
  4. 无法删除原备份数据库中的用户名,提示“因为选定的用户拥有对象,所以无法除去该用户。”

     主要原因是原来的备份还原时保留了原用户的信息,导致产生孤立用户……

     这时候需要用sp_changeobjectowner 将对象的所有关系更改到另一个用户上,既更改数据库对象的所有者。

     格式:

sp_changeobjectowner [@objectname =] ''object'', [@newowner =] ''owner''

例子 在查询分析器中录入:

sp_changeobjectowner ''web102101.tablename'', ''dbo''

依次将所有的所属用户都改为dbo,然后现在数据库的用户中把孤立用户删除,再到安全中删除登陆信息。

并可以再依次创建新用户了……


PS:为验证所述查到“猴哥”zjcxc(邹建)大师兄的贵文:

孤立用户疑难解答
把数据库备份还原到另一个服务器时,可能会遇到孤立用户的问题。下面的方案显示并解决了这个问题:

通过执行 sp_addlogin,把登录 janetl 改名为 dbo。
sp_addlogin ''janetl'', ''dbo''

备份数据库。在本例中,备份 Northwind。
BACKUP DATABASE Northwind
TO DISK = ''c:\mssql\backup\northwnd''

除去刚刚备份的数据库。
DROP DATABASE Northwind

除去登录。
sp_droplogin ''janetl''

还原备份的数据库。
RESTORE DATABASE Northwind
FROM DISK = ''c:\mssql\backup\northwnd''

janetl 登录不能访问 Northwind 数据库,除非允许 guest 登录。尽管 janetl 登录已经删除,它仍然(作为一个孤立行)显示在 sysusers 表中:

USE Northwind
SELECT *
FROM sysusers
WHERE name = ''janetl''

解决孤立用户问题

用 sp_addlogin 添加一个临时登录。为孤立用户指定安全标识符 (SID)(从 sysusers)。
sp_addlogin @loginame = ''nancyd'',
@sid = 0x32C864A70427D211B4DD00104B9E8A00

用 sp_dropalias 除去属于别名 SID 的临时别名。
sp_dropalias ''nancyd''

用 sp_dropuser 除去原始用户(即现在的孤立用户)。
sp_dropuser ''janetl''

用 sp_dropuser 除去原始登录。
sp_droplogin ''nancyd''

--孤立用户的产生演示 --创建一个测试的数据库 CREATE DATABASE DB_test go --创建一个登录 EXEC sp_addlogin ''aa'' --设置登录 aa 的默认数据库为测试数据库 DB_test EXEC sp_defaultdb ''aa'',''DB_test'' go --切换到测试数据库 USE DB_test go --为登录 aa 在当前测试数据库中添加用户 EXEC sp_grantdbaccess ''aa'' go --至此,用户 aa 登录后,其默认的当前数据库就是 DB_test --我们可以在查询分析器,使用用户 aa 登录一下,来验证我们的测试环境 --备份测试数据库,为下面的测试做准备 BACKUP DATABASE DB_test TO DISK=''c:\DB_test.bak'' WITH INIT go /*=================== 产生孤立用户 ======================*/ --切换到 master 数据库 USE master go --删除测试数据库 DROP DATABASE DB_test go --删除登录 aa EXEC sp_droplogin ''aa'' go /*=================== 孤立用户表现形式1 ======================*/ --还原测试数据库 RESTORE DATABASE DB_test FROM DISK=''c:\DB_test.bak'' go --切换到测试数据库 USE DB_test go --查看用户信息 select name from sysusers where islogin=1 --我们会发现,虽然我们已经将登录 aa 删除了,但用户 aa 仍然存在于数据库中 --尝试一下,用 aa 登录,被告知登录失败 go --再把删除的登录添加回去 EXEC sp_addlogin ''aa'' --设置登录 aa 的默认数据库为测试数据库 DB_test EXEC sp_defaultdb ''aa'',''DB_test'' --再次登录,被告知无法打开默认数据库,登录失败 go --于是把默认数据库改为 master EXEC sp_defaultdb ''aa'',''master'' --这次再登录,就可以登录了 go --尝试切换到测试数据库 DB_test USE DB_test --得到错误信息: 服务器用户 ''aa'' 不是数据库 ''DB_test'' 中的有效用户。 --看来用户 aa 与登录 aa 失去了联系 go --尝试重新为登录 aa 添加用户 aa EXEC sp_grantdbaccess ''aa'' --得到错误信息:当前数据库中已存在用户或角色 ''aa''。 --这次我们换个顺序,先建立登录,再恢复数据库,看能否使登录与用户自动建立回联系 --做这个测试之前,先清理测试环境,即做前面的<产生孤立用户>步骤,然后再开始测试 --先添加登录 EXEC sp_addlogin ''aa'' go --还原测试数据库 RESTORE DATABASE DB_test FROM DISK=''c:\DB_test.bak'' go --切换到测试数据库 USE DB_test go --查看用户信息 select name from sysusers where islogin=1 --我们会发现,用户 aa 存在于数据库中 --尝试一下,用 aa 登录,并切换到 DB_test --结果是登录成功,访问 DB_test 出现和测试1一样的错误
/*==== 解决上面提到的孤立用户的问题 ====*/ --用sa 登录系统,查询孤立用户的sid --如果你已经预先在sql中创建了aa这个登录,则先删除它 EXEC sp_droplogin ''aa'' DECLARE @sid BINARY(16) SELECT @sid=sid FROM DB_test..sysusers WHERE name=''aa'' and islogin=1 exec sp_addlogin @loginame = ''aa'',@sid = @sid

原理,先还原数据库.

再找出孤立用户的用户名及sid
然后在sql实例中创建该用户的登录,创建时强制指定sid

返回】 【关闭