問題

最近公司很多數據庫在上雲，也有一部分在下雲。這期間出現了很多問題，其中一個比較噁心的問題就是“孤立用戶”。當數據庫備份還原以後用以前的用戶發現不能登錄。一開始以為是登錄賬號沒有創建，然後重新創建登錄賬號，然後再授權給數據庫，此時又出錯，說用戶已經存在。我這才引起注意，開始搜索這個資料，原來這就是因為臭名昭著的孤立用戶引起的。

什麼是孤立的SQL用戶?

那麼孤立用戶又是什麼東西那？一個孤立用戶就是一個數據庫用戶，同時沒有SQL Server的登錄權限。

在實際生產中有很多產生孤立用戶的原因，最為主要的方式就是備份還原到不同的服務器實例時。還原數據庫的時候回將數據庫和用戶一同還原到新的數據庫上，但是服務器的登錄賬戶卻沒有一同還原（也不需要這麼做）。如果數據庫相同服務器那麼皆大歡喜，因為用戶沒有變。如果是不同服務器，此時登錄賬戶中沒有了數據庫用的名稱，即使你創建了相同的名稱但是他們的ID也是不同的導致他們不能關聯起來。此時就導致了數據庫的用戶被孤立，也不能訪問。此時我們需要做的就是找出孤立用戶修改或者刪除重建。

下圖中是外國網友列出可能的產生孤立用戶的原因（很詳細）：

查找數據庫中的孤立用戶

我打算寫一個腳本實現兩個主要目的，一是找到一個實例內所有的孤立用戶；第二是按需求刪除這些用戶。從網上找了不少腳本和博客發現都不能實現。所以我自己寫了一個親測可用。這個腳本的麻煩在於當刪除用戶時，這個用戶擁有自己的對象，並且不能drop掉，只能先刪除這個對象或者改變對象和用戶之間的關係。在下面的例子中所有的用戶擁有一個架構，腳本必須去處理這個用戶的架構。用腳本實現把孤立用戶存儲到一個臨時表內，然後根據臨時表的用戶信息刪除架構和用戶。

查找孤立用戶的腳本

Use master
Go
Create Table #Orphans 
 (
 RowID int not null primary key identity(1,1) ,
 TDBName varchar (100),
 UserName varchar (100),
 UserSid varbinary(85)
 )
SET NOCOUNT ON 
 DECLARE @DBName sysname, @Qry nvarchar(4000)
 SET @Qry = ''
 SET @DBName = ''
 WHILE @DBName IS NOT NULL
 BEGIN
 SET @DBName = 
 (
 SELECT MIN(name) 
 FROM master..sysdatabases  

 WHERE
 /** to exclude named databases add them to the Not In clause **/
 name NOT IN 
 (
 'model', 'msdb', 
 'distribution'
 ) And 
 DATABASEPROPERTY(name, 'IsOffline') = 0 
 AND DATABASEPROPERTY(name, 'IsSuspect') = 0 
 AND name > @DBName
 )
 IF @DBName IS NULL BREAK
 
 Set @Qry = 'select ''' + @DBName + ''' as DBName, name AS UserName, 
 sid AS UserSID from [' + @DBName + ']..sysusers 
 where issqluser = 1 and (sid is not null and sid <> 0x0) 
 and suser_sname(sid) is null order by name'
 Insert into #Orphans Exec (@Qry)
 
 End
Select * from #Orphans

如何刪除用戶（這部分切記酌情使用，先與使用人員或者DBA確認孤立用戶已經用了再進行刪除。並確認其架構對象不收影響）

接著上面的腳本，我們把用戶從臨時表中取出來進行循環處理。


Declare @SQL as varchar (200)
Declare @DDBName varchar (100)
Declare @Orphanname varchar (100)
Declare @DBSysSchema varchar (100)
Declare @From int
Declare @To int
Select @From = 0, @To = @@ROWCOUNT 
from #Orphans
--Print @From
--Print @To
While @From <= @To
 Begin
 Set @From = @From + 1
  

 Select @DDBName = TDBName, @Orphanname = UserName from #Orphans
 Where RowID = @From
 
 Set @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 Catch
 
 End
 
Drop table #Orphans