处理SQL Server数据库中的孤立用户(图)

处理SQL Server数据库中的孤立用户(图)

把数据库从一个服务器实例附加和恢复到另一个实例中是数据库管理员执行的常见的任务。附加或者恢复一个数据库之后，之前在数据库中创建和配置的登录名已经不能访问了。这个问题最常见的症状是应用程序会遇到登录失败的错误，或者是当你试着把登录名添加到数据库中时，你可能会得到一个信息比如这个用户已经在这个数据库中存在。当你执行一个附加或者一个恢复时，这是很常见的一种情况，那么你如何解决这个问题呢?

　　专家解答

　　当数据库从一个服务器迁移到另一个服务器时，存储在主从数据库中的登录名ids与存储在每个用户数据库中的登录名ids不符合。正如上面所说的，附加或恢复一个数据库之后你会看到的一些错误包括：

Msg 229, Level 14, State 1 　　%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中的差异的脚本

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

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

　　

 

 

 

 

 

 

 

　　现在我们对这个问题有了更深入的了解，现在是时候利用一些有用的命令去分析和解决问题了。

　　我已经用以上四个用户把AdventureWorks数据库从一个实例还原到另一个实例上。现在为了分析在我的还原数据库中有多少个孤立用户，我将运行以下的T-SQL命令，这些命令将产生所有孤立用户的列表并且在这个例子中所有的用户都是在孤立的。

　　用来产生孤立用户列表的命令

USE adventureWorks 　　GO 　　sp_change_users_login @Action='Report' 　　GO

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

　　用来映射一个孤立用户的命令

USE AdventureWorks 　　GO 　　sp_change_users_login @Action='update_one', 　　@UserNamePattern='TestUser1', 　　@LoginName='TestUser1' 　　GO

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

　　

 

　　用来映射一个孤立用户的命令

EXEC sp_change_users_login 'Auto_Fix', 'TestUser2' 　　GO

　　这两种命令都可以把用户映射到登录名，并且它们将不再是孤立的。

　　如果一个登录名不存在，你必须在做映射之前先创建这个登录名。创建登录名的一种便捷方式是使用以下将会创建登录名接着把登录名映射到用户的命令。

　　用来把一个孤立用户映射到一个还未出现但是将要创建的登录名的命令

EXEC sp_change_users_login 'Auto_Fix', 'TestUser3', null,'pwd' 　　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 登录名的密码可以由数据库用户所使用。

http://www.anqn.com/asp/2009-07-29/a09113968.shtml