問題

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

什么是孤立的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

腳本中需要注意的事項

首先如果有些數據庫的孤立用戶不想處理那么在插入臨時表時可以提前通過NOT IN語句排除數據庫。在刪除的孤立用戶同時，也會刪除孤立用戶擁有的架構。需要引起注意。這個腳本將不會檢查其他可能被用戶擁有的對象。我已經在sql server 2005/2008/2014上進行了測試，請大家知悉。

總結

在上云或者數據庫遷移的時候一旦發現這類錯誤往往會出現一些難以預料的問題，我建議。可以先用查詢的語句進行查詢看看具體哪些用戶是孤立用戶，哪些需要區別對待，在進行其他處理。腳本是大大減少了自己挨個查詢的時間，但是也提高了風險，請大家酌情使用。