理解和处理SQL Server数据库中的孤立用户

问题  
把数据库从一个服务器实例附加和恢复到另一个实例中是数据库管理 员执行的常见的任务。附加或者恢复一个数据库之后，之前在数据库中创建和配 置的登录名已经无法访问了。这个问题最常见的症状是应用程序会碰到登录失败 的错误，或者是当你试着把登录名添加到数据库中时，你可能会得到一个信息比 如这个用户已经在这个数据库中存在。当你执行一个附加或者一个恢复时，这是 很常见的一种状况，那么你怎么解决这个问题呢?  
专家解答  
当数据 库从一个服务器迁移到另一个服务器时，存储在主从数据库中的登录名ids与存储 在每个用户数据库中的登录名ids不符合。正如上面所说的，附加或恢复一个数据 库之后你会看到的一些错误包含：  

普通浏览复制代码保存代码打印代码

1. 
2. Msg 229, Level 14, State 1 
3. %s permission denied on object %.*s, database %.*s, owner %.*s

<br /> Msg 229, Level 14, State 1 <br /> %s permission denied on object %.*s, database %.*s, owner %.*s

或 者  
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.  
没有正 确的理解和适当的计划，你可能会碰到这个问题。你可能会删除和重新创建这个 用户，但是你将开释所有配置的权限。所以一个正确的链接机制是需求的，因此 要保存权限。  
你可能看到的一些可能的错误信息包含  


  


  
  
在开始这个问题的解决方案之前，最好看看反方向的问题。存储在主从 数据库中的SQL Server 登录名映射到个别的数据库中。SQL Server 登录名通过 运用映射到适当的SQL Server 登录名的数据库用户来访问个别的数据库。有两种 状况例外，那就是来宾帐户和Microsoft Windows组成员身份。服务器实例上的 SQL Server 2005登录名在sys.server_principals系统目录视图和sys.syslogins 视图上是可见的。对于SQL Server 2000，你可以在sysxlogins表中得到SQL Server登录名信息。  
另一方面，映射到另一个数据库用户的信息存储在系 统表sysusers的数据库中。它包含数据库用户名和相对应的SQL Server登录名的 安全标示符(SID)。这个数据库用户的权限用于在数据库中授权。  


  
所以我们可以说，每次我们创建一个SQL Server登录名，就可以在SQL Server 2005 的 sys.server_principals系统目录视图或者sys.syslogins视图上 看到它。一个数据库中的sysusers表的表项链接到上图显示的SQL Server 登录名 中。这个链接通过一个名为SID的栏创建。  
假如我们通过某些过程把我们 的数据库迁移到另一个SQL Server实例上，那么这个新的服务器可能会或可能不 会具有相同的登录名并且这些登录名的SIDs可能与原来服务器中的登录名的SIDs 不一样。这意味着，迁移的数据库中的sysusers表具有与新的服务器中的主从数 据库的登录名信息不符的SIDs。因此，我们得到孤立用户(orphaned users)。  
以下将作为一个例子，我在AdventureWorks数据库中创建和配置四个具有 不同权限的用户。这些用户是TestUser1，TestUser2，TestUser3和TestUser4。 当我把这个数据库备份还原到SQL Server 2005实例中，固然用户在 AdventureWorks数据库中显示并且登录名存在于新的服务器中，但是这些登录名 没有一个访问到新的恢复数据库中。  
所以记住这个情景，让我们先来执行 一些查询，看看SQL Server 登录名SIDs(假如SQL Server登录名显示了)和 TestUser3的数据库用户SIDs之间的差异。  
用来观看SID中的差异的脚本  

普通浏览复制代码保存代码打印代码

1. USE MASTER 
2. 
3. GO 
4. 
5. SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins 
6. 
7. WHERE [name] = 'TestUser3' 
8. 
9. GO 
10. 
11. USE AdventureWorks 
12. 
13. GO 
14. 
15. SELECT name DataBaseID,SID as DatabaseSID FROM sysusers 
16. 
17. WHERE [name] = 'TestUser3' 
18. 
19. GO

USE MASTER <br /> <br /> GO <br /> <br /> SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins <br /> <br /> WHERE [name] = 'TestUser3' <br /> <br /> GO <br /> <br /> USE AdventureWorks <br /> <br /> GO <br /> <br /> SELECT name DataBaseID,SID as DatabaseSID FROM sysusers <br /> <br /> WHERE [name] = 'TestUser3' <br /> <br /> GO

  
以下的结果表明SQL Server 登录名中的SID和数据库用户id的SID是不一样的，这也表明了这正是造成 问题出现的原因。  


  
  
现在我们对这个问题有了更深入的了解，现在是时候利用一些有用的命 令去分析和解决问题了。  
我已经用以上四个用户把AdventureWorks数据库 从一个实例还原到另一个实例上。现在为了分析在我的还原数据库中有多少个孤 立用户，我将运行以下的T-SQL命令，这些命令将产生所有孤立用户的列表并且在 这个例子中所有的用户都是在孤立的。  
用来产生孤立用户列表的命令  

普通浏览复制代码保存代码打印代码

1. USE adventureWorks 
2. 
3. GO 
4. 
5. sp_change_users_login @Action='Report' 
6. 
7. GO

USE adventureWorks <br /> <br /> GO <br /> <br /> sp_change_users_login @Action='Report' <br /> <br /> GO

  


  
现在我们已经有了孤立用户的列表，我们可以开始着手解决这个问题。 为了克服这个问题，你需求把用户(来自sysusers表)的SIDs链接到主从数据库的 有效登录名中。下面的命令TestUser1指定的数据库用户重新映射到TestUser1指 定的服务器登录名账户。  
用来映射一个孤立用户的命令  

普通浏览复制代码保存代码打印代码

1. USE AdventureWorks 
2. 
3. GO 
4. 
5. sp_change_users_login @Action='update_one', 
6. 
7. @UserNamePattern='TestUser1', 
8. 
9. @LoginName='TestUser1' 
10. 
11. GO

USE AdventureWorks <br /> <br /> GO <br /> <br /> sp_change_users_login @Action='update_one', <br /> <br /> @UserNamePattern='TestUser1', <br /> <br /> @LoginName='TestUser1' <br /> <br /> GO

  
或者假如你确定SQL Server 登录名和映射的数据库孤立用户名一样，那么接着你可以运用更短的命令 ，比如以下TestUser2的例子。  
用来映射一个孤立用户的命令  

普通浏览复制代码保存代码打印代码

1. EXEC sp_change_users_login 'Auto_Fix', 'TestUser2' 
2. 
3. GO

EXEC sp_change_users_login 'Auto_Fix', 'TestUser2' <br /> <br /> GO

  
这两种命令都可以把用户映射到登录名，并且它们 将不再是孤立的。  
假如一个登录名不存在，你必须在做映射之前先创建这 个登录名。创建登录名的一种便捷方式是运用以下将会创建登录名接着把登录名 映射到用户的命令。  
用来把一个孤立用户映射到一个还未出现但是将要创 建的登录名的命令  

普通浏览复制代码保存代码打印代码

1. EXEC sp_change_users_login 'Auto_Fix', 'TestUser3', null,'pwd' 
2. 
3. GO

  EXEC sp_change_users_login 'Auto_Fix', 'TestUser3', null,'pwd' <br /> <br /> GO
  
总结应用的T-SQL  
在 以上过程中，运用了存储过程sp_change_users_login。变量[ @Action ]指定了 这个存储过程的正确用法。它接受像varchar(10)如此的参数并且有以下的一种值 ：  
l 假如参数是Auto_Fix，那么数据库用户从相同名称的SQL Server 登 录名映射。假如这个登录名没有显示，它也可能会产生登录名。  
l 假如参 数是Report，它列出孤立用户和它们的安全标识符(SID)。  
l 假如参数是 Update_One，它从指定的数据库链接到现有的SQL Server 登录名。  
一些 留意事项  
l sp_change_users_login要求是db_owner固定数据库角色的组 成成员，只有成为db_owner固定数据库角色的组成成员才能指定Auto_Fix选项。  
l 当映射孤立用户时，主从数据库中的SID将分配给孤立用户，所以每次 一个数据库备附加或者还原时，SID在SQL Server 登录名和数据库用户之间都存 在差异。  
l 假如你有映射到一个数据库用户的不同的Server 登录名，那 么不要运用Auto_Fix命令来链接。  
l 假如相应的SQL Server 登录名被删 除，那么一个用户也可能成为孤立的。  
l 固然这是很显而易见的，但是还 是有必要提出来，在重新链接之后，SQL Server 登录名的密码可以由数据库用户 所运用。