SQL Login 问题

 

当用户使用*.bak去还原时候,database 可能带有user,但这个还没有注册到login里面,可以用:

 

EXEC sp_change_users_login 'Auto_Fix','username'

 

去自动更新Login

 

http://www.seattletech.com/forum/viewtopic.php?t=71

 

After the users are "fixed" then you need to assign them to their roles.

Here is an explanation of what has happened to cause this error. When you restored the databases to the second server, the users in them remained intact. However, the users are pointing to logins that do not exist or exist, but have different SIDs. Thus the users in the databases are "inconsistent" or "orphaned" and are not visible except if you query directly the sysusers table.

 

SQL Server offers a nice stored procedure, which resolves this problem - sp_change_users_login. What it basically does is if the login already exists, the user is modified in the sysusers table to match the right SID. If the login does not exist, it is created automatically and appropriate rights to the database are granted.

 

http://www.egocbd.com/helpsql/023/413.shtml

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

note: from envykok

及时存在相同的帐户名，但SID (安全标识符 ) 不同，则数据库认为是不同用户，SID : USE [XXX ] GO SELECT   * FROM sysusers ; USE [MASTER] GO SELECT * FROM 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中

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