孤立用户解决办法

最新推荐文章于 2021-04-09 17:36:37 发布

weixin_30642305

最新推荐文章于 2021-04-09 17:36:37 发布

阅读量196

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/lanshh/archive/2007/02/08/644852.html

解决方法：

1.建立与孤立用户同名的登陆用户（密码随便），改变对象的属主

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

如：

use authors

exec sp_changeobjectowner 'authors', 'dbo';
GO

缺点：当需要修改的数据库对象很多时，麻烦！！

2.sp_change_users_login 'Auto_Fix', 'test', NULL, 'testpassword'

      注：test为孤立用户名 testpassword为孤立用户密码
资料：
    sp_change_users_login (Transact-SQL) 将现有数据库用户映射到 SQL Server 登录名。
    注： sp_change_users_login 不能用于 Windows 主体创建的 SQL Server 登录名。

Changes the relationship between a Microsoft® SQL Server™ login and a SQL Server user in the current database.

Syntax

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

Arguments

[@Action =] 'action' Describes the action to be performed by the procedure. action is varchar(10), and can be one of these values.

Value Description Auto_Fix Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.

user must be a valid user in the current database, and login must be NULL, a zero-length string (‘‘), or not specified.

Report Lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login.

user and login must be NULL, a zero-length string (‘‘), or not specified.

Update_One Links the specified user in the current database to login. login must already exist. user and login must be specified.
[@UserNamePattern =] 'user' Is the name of a SQL Server user in the current database. user is sysname, with a default of NULL. sp_change_users_login can only be used with the security accounts of SQL Server logins and users; it cannot be used with Microsoft Windows NT® users. [@LoginName =] 'login' Is the name of a SQL Server login. login is sysname, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name Data type Description UserName sysname Login name. UserSID varbinary(85) Login security identifier.

Remarks

Use this procedure to link the security account for a user in the current database with a different login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing the user’s permissions.

login cannot be sa, and user cannot be the dbo, guest, or INFORMATION_SCHEMA users.

sp_change_users_login cannot be executed within a user-defined transaction.

Permissions

Any member of the public role can execute sp_change_users_login with the Report option. Only members of the sysadmin fixed server role can specify the Auto_Fix option. Only members of the sysadmin or db_owner roles can specify the Update_One option.

Examples

A. Show a report of the current user to login mappings

This example produces a report of the users in the current database and their security identifiers.

EXEC sp_change_users_login 'Report'

B. Change the login for a user

This example changes the link between user Mary in the pubs database and the existing login, to the new login NewMary (added with sp_addlogin).

--Add the new login.

USE master

go

EXEC sp_addlogin 'NewMary'

go

--Change the user account to link with the 'NewMary' login.

USE pubs

go

EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'

Refer:http://msdn2.microsoft.com/zh-cn/library/ms174378.aspx#

转载于:https://www.cnblogs.com/lanshh/archive/2007/02/08/644852.html