Chapter 2 User Authentication, Authorization, and Security（11）：在已还原的数据库中修正登录映射错误

原文出处：http://blog.csdn.net/dba_huangzj/article/details/39496517，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

未经作者同意，任何人不得以“原创”形式发布，也不得已用于商业用途，本人不负责任何法律责任。

        前一篇：http://blog.csdn.net/dba_huangzj/article/details/39473895

 

前言：

 

如果你把非包含数据库从一个服务器移到另外一个服务器，不管是备份还原还是分离附加，都有可能导致SQL用户变成孤立用户，意味着他们没有对应的登录关联。因为登录名和用户之间的映射是基于SID的，即使新服务器上有相同的登录名，但是由于SID不同，也会使得用户无法被识别，从而形成孤立用户。

如果在同一个域中迁移，那么映射问题仅会影响SQL登录，因为域账号（Windows身份验证）的SID在活动目录中是相同的。

 

实现：

 

可以用下面步骤来检查和修复通过还原或附加的形式把数据库迁移到新服务器后的孤立问题：

1.识别：

SELECT  dp.name , 
        dp.sid 
FROM    sys.database_principals dp 
        LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid 
WHERE   sp.sid IS NULL 
        AND dp.type_desc = 'SQL_USER' 
        AND dp.principal_id > 4;

 

2.修复：

ALTER USER Fred WITH LOGIN = Fred

 

原理：

 

在不涉及包含数据库时，有几种方法可以避免或者修正孤立问题，对于SQL Server 2005 SP2之前的版本，可以使用系统存储过程：sp_change_users_login。实现，如：

USE marketing; 
exec sp_change_users_login @Action='Report';

 

如果存在孤立帐号，将会列出这些帐号的名字和SID出来，可以使用update_one或者auto_fix来修复：

EXEC sp_change_users_login @Action = 'update_one', @UserNamePattern ='fred', @LoginName = 'fred'; 
EXEC sp_change_users_login @Action = 'Auto_fix', @UserNamePattern = 'fred', @Password = 'I am s3cr3t !';
EXEC sp_change_users_login @Action = 'Auto_fix', @UserNamePattern = 'fred';

其中auto_fix会自动映射到相同的登录名中，如果登录名不存在，将会创建，并以你定义在@password中的值作为新的密码。

 

更多：

 

在很多情况下，数据库的onwer可能也会变成孤立，可以用下面语句检查：

SELECT SUSER_SNAME(owner_sid), name FROM sys.databases;

 

如果返回的结果中第一行为null，意味着数据库的onwer也是孤立，需要使用下面语句修复：

ALTER AUTHORIZATION ON DATABASE::marketing TO sa;

下一篇： http://blog.csdn.net/dba_huangzj/article/details/39548665