sql server还原数据库后的孤立用户

wxmiss

前段时间，服务器当机，找了半天文件，结果数据库的文件找回，重安装了系统遇到SQLSERVER孤立账号问题！网上找了个文章解决！呵呵，贴来大家学习！
MSSQL备份移植到另一服务器还原时容易遇到的问题……
MSSQL备份移植到另一服务器还原时容易遇到的问题，尤其是从虚拟主机备份回来的数据库在本机还原的问题…

会出现用SQL原来的用户名和密码无效的情况
无法删除某一个系统表
用sa连接做SELECT时提示表名无效
无法删除原备份数据库中的用户名，提示“因为选定的用户拥有对象，所以无法除去该用户。”
主要原因是原来的备份还原时保留了原用户的信息，导致产生孤立用户……

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

原理,先还原数据库.

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


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/treaturebea ... /07/24/1704909.aspx

wxmiss

前段时间，服务器当机，找了半天文件，结果数据库的文件找回，重安装了系统遇到SQLSERVER孤立账号问题！网上找了个文章解决！呵呵，贴来大家学习！
MSSQL备份移植到另一服务器还原时容易遇到的问题……
MSSQL备份移植到另一服务器还原时容易遇到的问题，尤其是从虚拟主机备份回来的数据库在本机还原的问题…

会出现用SQL原来的用户名和密码无效的情况
无法删除某一个系统表
用sa连接做SELECT时提示表名无效
无法删除原备份数据库中的用户名，提示“因为选定的用户拥有对象，所以无法除去该用户。”
主要原因是原来的备份还原时保留了原用户的信息，导致产生孤立用户……

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

格式：

sp_changeobjectowner [@objectname =] &#39;object&#39;, [@newowner =] &#39;owner&#39; 例子 在查询分析器中录入：

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

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


--------------------------------------------------------------------------------

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

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

通过执行 sp_addlogin，把登录 janetl 改名为 dbo。
sp_addlogin &#39;janetl&#39;, &#39;dbo&#39;

备份数据库。在本例中，备份 Northwind。
BACKUP DATABASE Northwind
TO DISK = &#39;c:\\mssql\\backup\\northwnd&#39;

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

除去登录。
sp_droplogin &#39;janetl&#39;

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

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

USE Northwind
SELECT *
FROM sysusers
WHERE name = &#39;janetl&#39;

解决孤立用户问题

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

用 sp_dropalias 除去属于别名 SID 的临时别名。
sp_dropalias &#39;nancyd&#39;

用 sp_dropuser 除去原始用户（即现在的孤立用户）。
sp_dropuser &#39;janetl&#39;

用 sp_dropuser 除去原始登录。
sp_droplogin &#39;nancyd&#39;


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

原理,先还原数据库.

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


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/treaturebea ... /07/24/1704909.aspx

wxmiss

前段时间，服务器当机，找了半天文件，结果数据库的文件找回，重安装了系统遇到SQLSERVER孤立账号问题！网上找了个文章解决！呵呵，贴来大家学习！
MSSQL备份移植到另一服务器还原时容易遇到的问题……
MSSQL备份移植到另一服务器还原时容易遇到的问题，尤其是从虚拟主机备份回来的数据库在本机还原的问题…

会出现用SQL原来的用户名和密码无效的情况
无法删除某一个系统表
用sa连接做SELECT时提示表名无效
无法删除原备份数据库中的用户名，提示“因为选定的用户拥有对象，所以无法除去该用户。”
主要原因是原来的备份还原时保留了原用户的信息，导致产生孤立用户……

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

格式：

sp_changeobjectowner [@objectname =] &#39;object&#39;, [@newowner =] &#39;owner&#39; 例子 在查询分析器中录入：

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

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


--------------------------------------------------------------------------------

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

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

通过执行 sp_addlogin，把登录 janetl 改名为 dbo。
sp_addlogin &#39;janetl&#39;, &#39;dbo&#39;

备份数据库。在本例中，备份 Northwind。
BACKUP DATABASE Northwind
TO DISK = &#39;c:\\mssql\\backup\\northwnd&#39;

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

除去登录。
sp_droplogin &#39;janetl&#39;

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

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

USE Northwind
SELECT *
FROM sysusers
WHERE name = &#39;janetl&#39;

解决孤立用户问题

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

用 sp_dropalias 除去属于别名 SID 的临时别名。
sp_dropalias &#39;nancyd&#39;

用 sp_dropuser 除去原始用户（即现在的孤立用户）。
sp_dropuser &#39;janetl&#39;

用 sp_dropuser 除去原始登录。
sp_droplogin &#39;nancyd&#39;


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

原理,先还原数据库.

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


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/treaturebea ... /07/24/1704909.aspx