Sql Server 孤立使用者解決辦法

Sql Server 孤立使用者 是我們經常遇到的事情，今天詳細的梳理了下，希望能幫到你

當把使用者資料庫從一台 Sql Server 使用備份和恢複的方式遷移到另一台伺服器。資料庫恢複以後，原先使用者定義的一些其他資料庫使用者，就無法在新伺服器上繼續使用了。尤其對一些Sql Ser 帳號，這些就是孤立使用者。

Sql Server 的使用者安全管理分兩層，整個 Sql Server 伺服器一層，每個資料庫一層。一個使用者，在每一層上都有帳號，在兩個層面上都會分配不同的權利。在伺服器層的帳號，交登入帳號(Login)，可以設定它管理整個 Sql Server 伺服器，開啟跟蹤，修改 Sql Server 安全配置，備份所有資料庫等。在資料庫一層，叫資料庫使用者(DataBase User)，可以設定它對這個特定的資料庫有讀寫、修改表結構、預存程序定義等許可權。

伺服器層面的安全，是設定在伺服器的登陸帳號上的。所有登入帳號的資訊，可以查詢 master 資料庫裡面的 sys.server_principals 這張視圖。

資料庫層面有“資料庫使用者”這個概念。每個資料庫內部對象的安全性，例如表格的讀寫，是否訥訥感運行或修改預存程序等，都賦予在資料庫使用者上，儲存在這個資料庫內部。可以通過查詢 sys.database_principals 瞭解使用者資訊。

Sql Server 登入帳號必須要和某個資料庫使用者相對應後，才能被資料庫接納。這個對應，就是要使得使用者資料庫  sys.database_principals 裡面 SID 和 master 資料庫 sys.server_principals 裡的 SID 匹配起來。一個登入賬戶和資料庫使用者的名字可以不一樣，但是 SID 必須一樣。

當使用者資料庫恢複到新的伺服器上後，master 資料庫 sys.server_principals 裡並沒有這個帳號。但是使用者資料庫裡還有 這個資料庫使用者。於是這個使用者被“孤立”了。

解決辦法：

1. 備份時，要把系統資料庫一併備份了，再恢複的時間，也把資料庫恢複了

2. 通過 sp_change_users_login 來更改使用者

如要檢測孤立使用者：

User<Database_Name>;
Go;
sp_change_users_login @Action='Report';
Go;

 

恢複孤立使用者：

User<Database_Name>;
Go;
sp_change_users_login @Action='update_one',@UserNamePattern='<database_user>',@LoginName='<login_name>';
Go;

 

需要說明的是，sp_change_users_login 只能重新連結 Sql 登入帳號。對於資料庫使用者所對應的是 Windows 登入帳號，如果SID 不同，說明域也發生了變化，是不能通過這種方式串連在一起的。

 

更先進的方法

 

經過多次排除孤立使用者發現，一般孤立使用者都是好幾個同時出現的，而手工一個個來排除效率太低，又容易出錯，所以寫了一個比較智能的預存程序，一次排除當前庫中的所有孤立使用者：

 

declare @username nvarchar(50)

create table #temp_user(
  username nvarchar(50),
  UserSID int
) 

insert into #temp_user exec sp_change_users_login @Action='Report'

declare temp_cursor cursor for
select username from #temp_user

open temp_cursor
fetch next from temp_cursor into @username
WHILE (@@FETCH_STATUS=0)
begin
exec  sp_change_users_login 'Auto_Fix', @username, NULL, @username;
exec  sp_change_users_login @Action='update_one',@UserNamePattern=@username,@LoginName=@username;
 fetch next from temp_cursor into @username
end 
close temp_cursor
deallocate temp_cursor
drop table  #temp_user

 

通過 "孤立使用者" 的處理可以看出來：

1. Sql Server 的安全分兩層，分別為：Server 和 DataBase

2. 備份和恢複資料庫是很重要的

 

本文出處：Sql Server 孤立使用者解決辦法