齐心 2007-4-6 14:53
MSSQL备份移植到另一服务器还原时容易遇到的问题
MSSQL备份移植到另一服务器还原时容易遇到的问题，尤其是从虚拟主机备份回来的数据库在本机还原的问题…
[list=1][*]会出现用SQL原来的用户名和密码无效的情况[*]无法删除某一个系统表[*]用sa连接做SELECT时提示表名无效[*]无法删除原备份数据库中的用户名，提示“因为选定的用户拥有对象，所以无法除去该用户。”[/list] 主要原因是原来的备份还原时保留了原用户的信息，导致产生孤立用户……
这时候需要用sp_changeobjectowner 将对象的所有关系更改到另一个用户上，既更改数据库对象的所有者。
格式：
[color=#000000]sp_changeobjectowner [/color][color=#ff0000][[/color][color=#ff0000]@objectname =[/color][color=#ff0000]][/color]
[color=#ff0000]'[/color][color=#ff0000]object[/color][color=#ff0000]'[/color][color=#000000], [/color][color=#ff0000][[/color][color=#ff0000]@newowner =[/color][color=#ff0000]][/color]
[color=#ff0000]'[/color][color=#ff0000]owner[/color][color=#ff0000]'[/color]
例子 在查询分析器中录入：
[color=#000000]sp_changeobjectowner [/color][color=#ff0000]'[/color][color=#ff0000]web102101.tablename[/color][color=#ff0000]'[/color][color=#000000], [/color][color=#ff0000]'[/color][color=#ff0000]dbo[/color][color=#ff0000]'[/color]
依次将所有的所属用户都改为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'
[color=#008080]--[/color][color=#008080]孤立用户的产生演示[/color]
[color=#008080]--[/color][color=#008080]创建一个测试的数据库[/color]
[color=#0000ff]CREATE[/color]
[color=#0000ff]DATABASE[/color][color=#000000] DB_test[/color][color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]创建一个登录[/color]
[color=#0000ff]EXEC[/color][color=#000000] sp_addlogin [/color][color=#ff0000]'[/color][color=#ff0000]aa[/color][color=#ff0000]'[/color]
[color=#008080]--[/color][color=#008080]设置登录 aa 的默认数据库为测试数据库 DB_test[/color]
[color=#0000ff]EXEC[/color][color=#000000] sp_defaultdb [/color][color=#ff0000]'[/color][color=#ff0000]aa[/color][color=#ff0000]'[/color][color=#000000],[/color][color=#ff0000]'[/color][color=#ff0000]DB_test[/color][color=#ff0000]'[/color]
[color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]切换到测试数据库[/color]
[color=#0000ff]USE[/color][color=#000000] DB_test[/color][color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]为登录 aa 在当前测试数据库中添加用户[/color]
[color=#0000ff]EXEC[/color][color=#000000] sp_grantdbaccess [/color][color=#ff0000]'[/color][color=#ff0000]aa[/color][color=#ff0000]'[/color]
[color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]至此,用户 aa 登录后,其默认的当前数据库就是 DB_test[/color][color=#008080]--[/color][color=#008080]我们可以在查询分析器,使用用户 aa 登录一下,来验证我们的测试环境[/color]
[color=#008080]--[/color][color=#008080]备份测试数据库,为下面的测试做准备[/color]
[color=#0000ff]BACKUP[/color]
[color=#0000ff]DATABASE[/color][color=#000000] DB_test [/color][color=#0000ff]TO[/color]
[color=#0000ff]DISK[/color][color=#808080]=[/color][color=#ff0000]'[/color][color=#ff0000]c:\DB_test.bak[/color][color=#ff0000]'[/color]
[color=#0000ff]WITH[/color][color=#000000] INIT[/color][color=#0000ff]go[/color]
[color=#008080]/*[/color][color=#008080]=================== 产生孤立用户 ======================[/color][color=#008080]*/[/color]
[color=#008080]--[/color][color=#008080]切换到 master 数据库[/color]
[color=#0000ff]USE[/color][color=#000000] master[/color][color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]删除测试数据库[/color]
[color=#0000ff]DROP[/color]
[color=#0000ff]DATABASE[/color][color=#000000] DB_test[/color][color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]删除登录 aa[/color]
[color=#0000ff]EXEC[/color][color=#000000] sp_droplogin [/color][color=#ff0000]'[/color][color=#ff0000]aa[/color][color=#ff0000]'[/color]
[color=#0000ff]go[/color]
[color=#008080]/*[/color][color=#008080]=================== 孤立用户表现形式1 ======================[/color][color=#008080]*/[/color]
[color=#008080]--[/color][color=#008080]还原测试数据库[/color]
[color=#0000ff]RESTORE[/color]
[color=#0000ff]DATABASE[/color][color=#000000] DB_test [/color][color=#0000ff]FROM[/color]
[color=#0000ff]DISK[/color][color=#808080]=[/color][color=#ff0000]'[/color][color=#ff0000]c:\DB_test.bak[/color][color=#ff0000]'[/color]
[color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]切换到测试数据库[/color]
[color=#0000ff]USE[/color][color=#000000] DB_test[/color][color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]查看用户信息[/color]
[color=#0000ff]select[/color][color=#000000] name [/color][color=#0000ff]from[/color][color=#000000] sysusers [/color][color=#0000ff]where[/color][color=#000000] islogin[/color][color=#808080]=[/color][b]1[/b]
[color=#008080]--[/color][color=#008080]我们会发现,虽然我们已经将登录 aa 删除了,但用户 aa 仍然存在于数据库中[/color][color=#008080]--[/color][color=#008080]尝试一下,用 aa 登录,被告知登录失败[/color]
[color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]再把删除的登录添加回去[/color]
[color=#0000ff]EXEC[/color][color=#000000] sp_addlogin [/color][color=#ff0000]'[/color][color=#ff0000]aa[/color][color=#ff0000]'[/color]
[color=#008080]--[/color][color=#008080]设置登录 aa 的默认数据库为测试数据库 DB_test[/color]
[color=#0000ff]EXEC[/color][color=#000000] sp_defaultdb [/color][color=#ff0000]'[/color][color=#ff0000]aa[/color][color=#ff0000]'[/color][color=#000000],[/color][color=#ff0000]'[/color][color=#ff0000]DB_test[/color][color=#ff0000]'[/color]
[color=#008080]--[/color][color=#008080]再次登录,被告知无法打开默认数据库,登录失败[/color]
[color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]于是把默认数据库改为 master[/color]
[color=#0000ff]EXEC[/color][color=#000000] sp_defaultdb [/color][color=#ff0000]'[/color][color=#ff0000]aa[/color][color=#ff0000]'[/color][color=#000000],[/color][color=#ff0000]'[/color][color=#ff0000]master[/color][color=#ff0000]'[/color]
[color=#008080]--[/color][color=#008080]这次再登录,就可以登录了[/color]
[color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]尝试切换到测试数据库 DB_test[/color]
[color=#0000ff]USE[/color][color=#000000] DB_test[/color][color=#008080]--[/color][color=#008080]得到错误信息: 服务器用户 'aa' 不是数据库 'DB_test' 中的有效用户。[/color][color=#008080]--[/color][color=#008080]看来用户 aa 与登录 aa 失去了联系[/color]
[color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]尝试重新为登录 aa 添加用户 aa[/color]
[color=#0000ff]EXEC[/color][color=#000000] sp_grantdbaccess [/color][color=#ff0000]'[/color][color=#ff0000]aa[/color][color=#ff0000]'[/color]
[color=#008080]--[/color][color=#008080]得到错误信息:当前数据库中已存在用户或角色 'aa'。[/color]
[color=#008080]--[/color][color=#008080]这次我们换个顺序,先建立登录,再恢复数据库,看能否使登录与用户自动建立回联系[/color]
[color=#008080]--[/color][color=#008080]做这个测试之前,先清理测试环境,即做前面的<产生孤立用户>步骤,然后再开始测试[/color]
[color=#008080]--[/color][color=#008080]先添加登录[/color]
[color=#0000ff]EXEC[/color][color=#000000] sp_addlogin [/color][color=#ff0000]'[/color][color=#ff0000]aa[/color][color=#ff0000]'[/color]
[color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]还原测试数据库[/color]
[color=#0000ff]RESTORE[/color]
[color=#0000ff]DATABASE[/color][color=#000000] DB_test [/color][color=#0000ff]FROM[/color]
[color=#0000ff]DISK[/color][color=#808080]=[/color][color=#ff0000]'[/color][color=#ff0000]c:\DB_test.bak[/color][color=#ff0000]'[/color]
[color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]切换到测试数据库[/color]
[color=#0000ff]USE[/color][color=#000000] DB_test[/color][color=#0000ff]go[/color]
[color=#008080]--[/color][color=#008080]查看用户信息[/color]
[color=#0000ff]select[/color][color=#000000] name [/color][color=#0000ff]from[/color][color=#000000] sysusers [/color][color=#0000ff]where[/color][color=#000000] islogin[/color][color=#808080]=[/color][b]1[/b]
[color=#008080]--[/color][color=#008080]我们会发现,用户 aa 存在于数据库中[/color][color=#008080]--[/color][color=#008080]尝试一下,用 aa 登录,并切换到 DB_test[/color][color=#008080]--[/color][color=#008080]结果是登录成功,访问 DB_test 出现和测试1一样的错误[/color]
[color=#008080]/*[/color][color=#008080]==== 解决上面提到的孤立用户的问题 ====[/color][color=#008080]*/[/color]
[color=#008080]--[/color][color=#008080]用sa 登录系统,查询孤立用户的sid[/color]
[color=#008080]--[/color][color=#008080]如果你已经预先在sql中创建了aa这个登录,则先删除它[/color]
[color=#0000ff]EXEC[/color][color=#000000] sp_droplogin [/color][color=#ff0000]'[/color][color=#ff0000]aa[/color][color=#ff0000]'[/color]
[color=#0000ff]DECLARE[/color][color=#000000] @sid [/color][b]BINARY[/b][color=#000000]([/color][b]16[/b][color=#000000])[/color][color=#0000ff]SELECT[/color][color=#000000] @sid[/color][color=#808080]=[/color][color=#000000]sid [/color][color=#0000ff]FROM[/color][color=#000000] DB_test..sysusers [/color][color=#0000ff]WHERE[/color][color=#000000] name[/color][color=#808080]=[/color][color=#ff0000]'[/color][color=#ff0000]aa[/color][color=#ff0000]'[/color]
[color=#808080]and[/color][color=#000000] islogin[/color][color=#808080]=[/color][b]1[/b]
[color=#0000ff]exec[/color][color=#000000] sp_addlogin @loginame [/color][color=#808080]=[/color]
[color=#ff0000]'[/color][color=#ff0000]aa[/color][color=#ff0000]'[/color][color=#000000],@sid [/color][color=#808080]=[/color][color=#000000] @sid[/color]
原理,先还原数据库.
再找出孤立用户的用户名及sid
然后在sql实例中创建该用户的登录,创建时强制指定sid
