sql server孤立用户和对象名无效问题的解决方法

本文转载自：http://hi.baidu.com/xcrypt/blog/item/5e99f3fdf0b7481108244d7f.html

 

SQL Server 2005 联机丛书（2008 年 11 月）

sp_change_users_login (Transact-SQL)

 

更新日期： 2006 年 12 月 12 日

将现有数据库用户映射到 SQL Server 登录名。

注意：
sp_change_users_login 不能与通过 Windows 主体创建的 SQL Server 登录名一起使用，也不能与使用 CREATE USER WITHOUT LOGIN 创建的用户一起使用。

 

Transact-SQL 语法约定

语法

sp_change_users_login [ @Action = ] 'action'       [ , [ @UserNamePattern = ] 'user' ]       [ , [ @LoginName = ] 'login' ]           [ , [ @Password = ] 'password' ]

参数

[ @Action = ] ' action '

说明过程要执行的操作。action 的数据类型为 varchar(10)。action 可具有下列值之一。

值	说明
Auto_Fix	将当前数据库的 sys.database_principals 系统目录视图中的用户项链接到同名的 SQL Server 登录名。如果不存在同名的登录名，将会创建一个。检查 Auto_Fix 语句的结果，确认实际链接是否正确。在对安全性较为敏感的情况下，要避免使用 Auto_Fix。 如果使用 Auto_Fix 时登录名尚不存在，则必须指定 user 和 password，否则必须指定 user，但 password 将被忽略。login 必须为 NULL。user 必须是当前数据库中的有效用户。不能将另一个用户映射到该登录名。
Report	列出当前数据库中未链接到任何登录名的用户以及相应的安全标识符 (SID)。user、login 和 password 必须为 NULL 或不指定。
Update_One	将当前数据库中的指定 user 链接到现有 SQL Server login。必须指定 user 和 login。password 必须为 NULL 或不指定。

[ @UserNamePattern = ] ' user '

当前数据库中的用户名。user 的数据类型为 sysname，默认值为 NULL。

[ @LoginName = ] ' login '

SQL Server 登录的名称。login 的数据类型为 sysname，默认值为 NULL。

[ @Password = ] ' password '

通过指定 Auto_Fix 创建的新 SQL Server 登录名分配的密码。如果已存在匹配的登录名，则映射该用户名与登录名且忽略 password。如果不存在匹配的登录名，则 sp_change_users_login 创建新的 SQL Server 登录名并分配 password 作为新登录名的密码。password 的数据类型为 sysname，且不能为 NULL。

安全说明：
请使用强密码。有关详细信息，请参阅强密码。

 

返回代码值

0（成功）或 1（失败）

结果集

列名	数据类型	说明
UserName	sysname	数据库用户名。
UserSID	varbinary(85)	用户的安全标识符。

备注

注意：
sp_change_users_login 不能用于将数据库用户映射到 Windows 级主体、证书或非对称密钥。

 

使用 sp_change_users_login 将当前数据库中的数据库用户链接到 SQL Server 登录名。如果用户登录名已更改，则使用 sp_change_users_login 将用户链接到新的登录，而不会丢失用户的权限。新的 login 不能为 sa，而 user 不能为 dbo、guest 或 INFORMATION_SCHEMA 用户。

不能在用户定义的事务中执行 sp_change_users_login。

权限

要求具有 db_owner 固定数据库角色中的成员资格。只有 sysadmin 固定服务器角色的成员才能指定 Auto_Fix 选项。

示例

A. 显示登录映射的当前用户的报告

以下示例生成当前数据库中未链接到任何登录名的用户及其安全标识符 (SID) 的报告。

EXEC sp_change_users_login 'Report';

B. 将数据库用户映射到新的 SQL Server 登录名

在以下示例中，数据库用户与新的 SQL Server 登录名关联。数据库用户 MB-Sales 首先映射到另一个登录名，然后重新映射到登录名 MaryB。

--Create the new login.
CREATE LOGIN MaryB WITH PASSWORD = '982734snfdHHkjj3';
GO
--Map database user MB-Sales to login MaryB.
USE AdventureWorks;
GO
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
GO

C. 自动将用户映射到登录名（必要时新建一个登录名）

以下示例显示如何使用 Auto_Fix 将现有用户映射到同名的登录名，以及如何在不存在登录名 Mary 的情况下，创建密码为 B3r12-3x$098f6 的 SQL Server 登录名 Mary。

USE AdventureWorks;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';
GO

------------结束--------------------

一、孤立帐户，就是某个数据库的帐户只有用户名而没有登录名，这样的用户在用户库的sysusers系统表中存在，而在master数据库的syslogins中却没有对应的记录。

孤立帐户的产生一般是以下两种：
1.将备份的数据库在其它机器上还原；
2.重装系统或SQL SERVER之后只还原了用户库

解决方法是使用sp_change_users_login来修复。

sp_change_users_login的用法有三种

用法1：
exec sp_change_users_login 'REPORT'
列出当前数据库的孤立用户

用法2：
exec sp_change_users_login 'AUTO_FIX','用户名'
可以自动将用户名所对应的同名登录添加到syslogins中
如果没有建立testuser的登录用户，还可以用
sp_change_users_login 'Auto_Fix', 'testuser', NULL, 'testpass'

用法3：
exec sp_change_users_login 'UPDATE_ONE','用户名','登录名'
将用户名映射为指定的登录名。

 

二、对象名'xxxx’无效的问题

这里可能会有人遇到对象名 ‘xxxx’无效的问题。系统表却不会产生这个问题，而用户表还要加上用户名，然后是表明才能访问，比如select * from author,会说对象名 author 无效，而用select * from test.author就可以访问，这个是用户首选身份的问题。

解决很简单，就是察看test登录用户是否具有dba的权限或者系统管理的权限，有的话去掉就行了。

因为如果用户有dba身份，那么它登陆后的默认表空间是dbo的系统表空间，所以去掉了之后，就会以正常的test表空间访问数据表了。