江苏龙网

  1. 主页 > 生活百科 > >

Sql Server中孤立的SQL用户查找和删除

SQL Server

问题最近公司很多数据库在上云，也有一部分在下云 。这期间出现了很多问题，其中一个比较恶心的问题就是“孤立用户” 。当数据库备份还原以后用以前的用户发现不能登录 。一开始以为是登录账号没有创建，然后重新创建登录账号，然后再授权给数据库，此时又出错，说用户已经存在 。我这才引起注意，开始搜索这个资料，原来这就是因为臭名昭著的孤立用户引起的 。
什么是孤立的SQL用户?那么孤立用户又是什么东西那？一个孤立用户就是一个数据库用户，同时没有SQL Server的登录权限 。
在实际生产中有很多产生孤立用户的原因，最为主要的方式就是备份还原到不同的服务器实例时 。还原数据库的时候回将数据库和用户一同还原到新的数据库上，但是服务器的登录账户却没有一同还原（也不需要这么做） 。如果数据库相同服务器那么皆大欢喜，因为用户没有变 。如果是不同服务器，此时登录账户中没有了数据库用的名称，即使你创建了相同的名称但是他们的ID也是不同的导致他们不能关联起来 。此时就导致了数据库的用户被孤立，也不能访问 。此时我们需要做的就是找出孤立用户修改或者删除重建 。
下图中是外国网友列出可能的产生孤立用户的原因（很详细）：

Sql Server中孤立的SQL用户查找和删除

文章插图
 
查找数据库中的孤立用户
我打算写一个脚本实现两个主要目的，一是找到一个实例内所有的孤立用户；第二是按需求删除这些用户 。从网上找了不少脚本和博客发现都不能实现 。所以我自己写了一个亲测可用 。这个脚本的麻烦在于当删除用户时，这个用户拥有自己的对象，并且不能drop掉，只能先删除这个对象或者改变对象和用户之间的关系 。在下面的例子中所有的用户拥有一个架构，脚本必须去处理这个用户的架构 。用脚本实现把孤立用户存储到一个临时表内，然后根据临时表的用户信息删除架构和用户 。
查找孤立用户的脚本
Use masterGoCreate Table #Orphans( RowID int not null primary key identity(1,1) , TDBName varchar (100), UserName varchar (100), UserSid varbinary(85) )SET NOCOUNT ONDECLARE @DBName sysname, @Qry nvarchar(4000) SET @Qry = '' SET @DBName = '' WHILE @DBName IS NOT NULL BEGIN SET @DBName =( SELECT MIN(name)FROM master..sysdatabasesWHERE /** to exclude named databases add them to the Not In clause **/ name NOT IN( 'model', 'msdb','distribution' ) AndDATABASEPROPERTY(name, 'IsOffline') = 0AND DATABASEPROPERTY(name, 'IsSuspect') = 0AND name > @DBName ) IF @DBName IS NULL BREAKSet @Qry = 'select ''' + @DBName + ''' as DBName, name AS UserName,sid AS UserSID from [' + @DBName + ']..sysuserswhere issqluser = 1 and (sid is not null and sid <> 0x0)and suser_sname(sid) is null order by name' Insert into #Orphans Exec (@Qry)EndSelect * from #Orphans如何删除用户（这部分切记酌情使用，先与使用人员或者DBA确认孤立用户已经用了再进行删除 。并确认其架构对象不收影响）
接着上面的脚本，我们把用户从临时表中取出来进行循环处理 。
Declare @SQL as varchar (200)Declare @DDBName varchar (100)Declare @Orphanname varchar (100)Declare @DBSysSchema varchar (100)Declare @From intDeclare @To intSelect @From = 0, @To = @@ROWCOUNT from #Orphans--Print @From--Print @ToWhile @From <= @To Begin Set @From = @From + 1Select @DDBName = TDBName, @Orphanname = UserName from #Orphans Where RowID = @FromSet @DBSysSchema = '[' + @DDBName + ']' + '.[sys].[schemas]' print @DBsysSchema Print @DDBname Print @Orphanname set @SQL = 'If Exists (Select * from ' + @DBSysSchema+ ' where name = ''' + @Orphanname + ''') Begin Use ' + @DDBName+ ' Drop Schema [' + @Orphanname + '] End' print @SQL Exec (@SQL)Begin Try Set @SQL = 'Use ' + @DDBName+ ' Drop User [' + @Orphanname + ']' Exec (@SQL) End Try Begin Catch End CatchEnd Drop table #Orphans脚本中需要注意的事项
首先如果有些数据库的孤立用户不想处理那么在插入临时表时可以提前通过NOT IN语句排除数据库 。在删除的孤立用户同时，也会删除孤立用户拥有的架构 。需要引起注意 。这个脚本将不会检查其他可能被用户拥有的对象 。我已经在sql server 2005/2008/2014上进行了测试，请大家知悉 。
总结
在上云或者数据库迁移的时候一旦发现这类错误往往会出现一些难以预料的问题，我建议 。可以先用查询的语句进行查询看看具体哪些用户是孤立用户，哪些需要区别对待，在进行其他处理 。脚本是大大减少了自己挨个查询的时间，但是也提高了风险，请大家酌情使用 。


推荐阅读


上一篇：春茶,如何正确且健康的品饮

下一篇：普洱茶的科学十年,回顾与展望