SQL Server資料庫中還原孤立使用者的方法集合

標籤：style   color   os   使用   io   strong   for   ar   資料   

SQL Server資料庫中還原孤立使用者的方法集合

雖然SQL Server現在搬遷的技術越來越多，內建的方法也越來越進階。

但是我們的SQL Server在搬遷的會出現很多孤立使用者，微軟沒有自動的處理。

因為我們的資料庫許可權表都不會在應用程式資料庫中，但是每次對資料庫作遷移的時候，單個資料庫卻帶著它的資料庫使用者物件。

並且我們在新的資料庫機器上也不能登入這些帳號，但是它卻靜悄悄的存在我們的資料庫中。

微軟以前提供的一個老的介面預存程序來處理這個問題。

sp_change_users_login

將現有資料庫使用者映射到 SQL Server 登入名。後續版本的 Microsoft SQL Server 將刪除該功能。請避免在新的開發工作中使用該功能，並著手修改當前還在使用該功能的應用程式。 請改用 ALTER USER。

雖然每次都在說以後的版本會不支援，但是用了好幾個版本還是支援的。

文法：

sp_change_users_login [ @Action = ] ‘action‘     [ , [ @UserNamePattern = ] ‘user‘ ]     [ , [ @LoginName = ] ‘login‘ ]     [ , [ @Password = ] ‘password‘ ][;]

參數：

[ @Action = ] ‘ action‘

說明過程要執行的操作。action 的資料類型為 varchar(10)。action 可具有下列值之一。

值	說明
Auto_Fix	將當前資料庫的 sys.database_principals 系統目錄檢視中的使用者項連結到同名的 SQL Server 登入名。如果不存在同名的登入名稱，將會建立一個。檢查Auto_Fix 語句的結果，確認實際連結是否正確。在對安全性較為敏感的情況下，要避免使用Auto_Fix。 如果使用 Auto_Fix 時登入名稱尚不存在，則必須指定 user 和 password，否則必須指定user，但password 將被忽略。login 必須為 NULL。user 必須是當前資料庫中的有效使用者。不能將另一個使用者映射到該登入名稱。
Report	列出當前資料庫中未連結到任何登入名稱的使用者以及相應的安全性識別碼 (SID)。user、login 和 password 必須為 NULL 或不指定。
Update_One	將當前資料庫中的指定 user 連結到現有 SQL Server login。必須指定 user 和 login。password 必須為 NULL 或不指定。

[ @UserNamePattern = ] ‘user‘

當前資料庫中的使用者名稱。user 的資料類型為 sysname，預設值為 NULL。

[ @LoginName = ] ‘login‘

SQL Server 登入的名稱。login 的資料類型為 sysname，預設值為 NULL。

[ @Password = ] ‘password‘

通過指定 Auto_Fix 建立的新 SQL Server 登入名分配的密碼。如果已存在匹配的登入名稱，則映射該使用者名稱與登入名稱且忽略password。如果不存在匹配的登入名稱，則 sp_change_users_login 建立新的 SQL Server 登入名並分配password作為新登入名稱的密碼。password 的資料類型為sysname，且不能為 NULL。

傳回碼值：

0（成功）或 1（失敗）

結果集：

列名	資料類型	說明
UserName	sysname	資料庫使用者名稱。
UserSID	varbinary(85)	使用者的安全性識別碼。

注釋：

使用 sp_change_users_login 將當前資料庫中的資料庫使用者連結到 SQL Server 登入名。如果使用者登入名稱已更改，則使用 sp_change_users_login 將使用者連結到新的登入，而不會丟失使用者的許可權。新的login 不能為 sa，而user不能為 dbo、guest 或 INFORMATION_SCHEMA 使用者。

sp_change_users_login 不能用於將資料庫使用者映射到 Windows 級主體、認證或非對稱金鑰。

sp_change_users_login 不能與通過 Windows 主體建立的 SQL Server 登入名一起使用，也不能與使用 CREATE USER WITHOUT LOGIN 建立的使用者一起使用。

不能在使用者定義的事務中執行 sp_change_users_login。

許可權：

需要 db_owner 固定資料庫角色的成員資格。只有 sysadmin 固定伺服器角色的成員才能指定 Auto_Fix 選項。

樣本：A. 顯示登入映射的目前使用者的報告

下例產生當前資料庫中的使用者及其安全性識別碼 (SID) 的報告。

	複製代碼
EXEC sp_change_users_login ‘Report‘;

B. 將資料庫使用者映射到新的 SQL Server 登入名

在以下樣本中，資料庫使用者與新的 SQL Server 登入名關聯。資料庫使用者 MB-Sales 首先映射到另一個登入名稱，然後重新對應到登入名稱MaryB。

	複製代碼
--Create the new login.CREATE LOGIN MaryB WITH PASSWORD = ‘982734snfdHHkjj3‘;GO--Map database user MB-Sales to login MaryB.USE AdventureWorks2008R2;GOEXEC sp_change_users_login ‘Update_One‘, ‘MB-Sales‘, ‘MaryB‘;GO

C. 自動將使用者映射到登入名稱（必要時建立一個登入名稱）

以下樣本顯示如何使用 Auto_Fix 將現有使用者映射到同名的登入名稱，以及如何在不存在登入名稱 Mary 的情況下，建立密碼為B3r12-3x$098f6 的 SQL Server 登入名Mary。

實戰：

    /*很多做DBA的朋友也許都會碰到一個這樣的問題，    在資料還原到其他伺服器時，登入名稱會丟失，重建非常麻煩，    特別是資料庫使用者較多的的情況，其實這個問題解決非常簡單，    sql server 2005 及以前的產品，    sql server 提供了 sp_change_users_login預存程序，(sql server 2008 須用ALTER USER)    sp_change_users_login有三種動作：report，update_one和auto_fix    具體用法：*//*1.列出當前資料庫中的孤立用戶*/    exec  sp_change_users_login 'report'/*2.如果已有登入用戶，將用戶名映射為指定的登入名稱*/    exec sp_change_users_login 'UPDATE_ONE','用戶名','登入名稱'    or     exec sp_change_users_login 'AUTO_FIX','用戶名'/*3.如果沒有建立登入用戶*/    exec sp_change_users_login 'Auto_Fix', '登入名稱', NULL, '登入密碼'/*--相同名字可以使用，處理孤立使用者--在單個資料庫上執行*/    exec sp_change_users_login 'AUTO_FIX','db_writer'    exec sp_change_users_login 'AUTO_FIX','db_reader'



SQL Server資料庫中還原孤立使用者的方法集合