SQL SERVER 資料庫遷移孤立使用者的解決方法

 如果用附加或者恢復備份的方法把資料庫從一個電腦移動到另外一個電腦,資料庫的使用者和SQL的登入會衝突,登入沒有辦法和使用者關聯,造成孤立使用者.

為了解決這個問題,首先在目標SQL系統上建立與使用者同名的登入,然後在資料庫上執行下面指令把使用者和登入關聯

use 資料庫

sp_change_users_login 'update_one', 'test', 'test'

(test使用者 和 test登入 關聯)

 

如果資料庫預先設定了大量的使用者許可權,可以用這個方法方便的移動到新伺服器上

 

更詳細的用法，請看SQL的幫助：

 

 

將現有資料庫使用者對映到 SQL Server 登入名。

注意： 
sp_change_users_login 不能用於 Windows 登入名。 

 

Transact-SQL 語法約定

語法
 
sp_change_users_login [ @Action = ] 'action'
    [ , [ @UserNamePattern = ] 'user' ]
    [ , [ @LoginName = ] 'login' ]
        [ , [ @Password = ] 'password' ]
 

備註
注意： 
sp_change_users_login 不能用於將資料庫使用者對映到 Windows 級主體、證照或非對稱金鑰。 

 

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

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

引數
[ @Action = ] 'action'
說明過程要執行的操作。action 的資料型別為 varchar(10)。action 可具有下列值之一。

值  說明 
Auto_Fix
 將當前資料庫的 sysusers 表中的使用者條目連結到同名的 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)
 使用者的安全識別符號。
 

許可權
需要 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 AdventureWorks;
GO
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
GO
 

C. 自動將使用者對映到登入名（必要時新建一個登入名）
以下示例顯示如何使用 Auto_Fix 將現有使用者對映到同名的登入名，以及如何在不存在登入名 Mary 的情況下，建立密碼為 B3r12-3x$098f6 的 SQL Server 登入名 Mary。

 

 複製程式碼
USE AdventureWorks;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';
GO
 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-621010/，如需轉載，請註明出處，否則將追究法律責任。