SQL server資料庫使用者映射到登陸使用者

標籤：database   sql   dba   資料庫   sql server   

    最近有朋友的SQL server資料庫意外中槍，幾經周折，資料將資料庫恢複到新環境。恢複後在資料庫層級有使用者名稱而執行個體層級則無相應的登陸使用者。這在SQL server資料庫中是一個比較普遍的情形。本文描述的是即是對這些孤立的賬戶通過系統過程sp_change_users_login來建立其映射關係。

1、sp_change_users_login的功能及其限制
  使用 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。
  sp_change_users_login在後續的版本將會被ALTER USER取代。

2、sp_change_users_login文法參考

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

3、參數說明
  [ @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。

4、使用樣本
a)、尋找當前資料庫孤立使用者
  exec sp_change_users_login ‘REPORT‘
  
  UserName  UserSID
  ---------- -----------------------
  csidbo    0xAFEEF9DA1BA20E43AC8B01C69574F91B

b)、將孤立使用者映射到同名(不存在)的登陸名
  -- 如下樣本，將建立一個新的登陸名為csidbo，且設定密碼為xxx
  -- 示範環境：Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1
  -- Author : Leshami
  -- Blog   : http://blog.csdn.net/leshami
  USE crmnew;
  GO
  EXEC sp_change_users_login ‘Auto_Fix‘, ‘csidbo‘, NULL, ‘xxx‘;
  GO

c)、將資料庫使用者映射到已存在的SQL Server 登入名

  USE crmnew;
  GO
  EXEC sp_change_users_login ‘Update_One‘, ‘csidbo‘, ‘csiuser‘
  GO

SQL server資料庫使用者映射到登陸使用者