SQL Server 2005 雙認證模式與 Login/User 雙層模型深度解析

核心區別：一圖勝千言

• 左側 (資料庫層級)：**Database User (使用者)** —— 權限主體，對應特定資料庫內的 Schema/物件權限 (SELECT/INSERT/EXEC...)。
• 右側 (伺服器層級)：**Login (登入)** —— 連線驗證主體，負責「能否連上這台 SQL Server」，**不包含任何資料庫物件權限**。

雙認證模式與 Login 建立

連線字串中的 User ID = Login，而非 User

Data Source=ServerName; Initial Catalog=DBName; User ID=Pumpkin; Password=!@#$%^&*

此處 `Pumpkin` 是 **Login**。驗證通過後，SQL Server 依據 **User-Login 對應關係** 切換至對應資料庫的 **User** 內容執行後續操作。

建立 Login 與 User 的 T-SQL 標準流程

-- 1. 建立伺服器級登入 (SQL 驗證範例)
CREATE LOGIN Pumpkin WITH PASSWORD = 'ComplexP@ssw0rd!';
-- Windows 驗證：CREATE LOGIN [DOMAIN\User] FROM WINDOWS;

-- 2. 切換至目標資料庫建立使用者
USE AdventureWorks;
GO
CREATE USER Pumpkin FOR LOGIN Pumpkin;
-- 或指定預設 Schema：CREATE USER Pumpkin FOR LOGIN Pumpkin WITH DEFAULT_SCHEMA = Sales;

-- 3. 授權 (權限給 User，而非 Login)
ALTER ROLE db_datareader ADD MEMBER Pumpkin;
GRANT EXECUTE ON SCHEMA::dbo TO Pumpkin;

User-Login 映射關係鐵律

1. 一對一：一個 User 在同一時間只能對應一個 Login。
2. 跨資料庫共用 Login：同一 Login 可在多資料庫建立同名/異名 User (如 `DB1.UserA`、`DB2.UserA` 皆對應 `LoginA`)。 3. 孤立使用者：還原資料庫後，User 存在但對應 Login 缺失 (SID 不匹配)。修復：`ALTER USER UserName WITH LOGIN = LoginName;` 或 `sp_change_users_login` (舊版)。 4. Contained Database (SQL 2012+)：可建立**無 Login 的資料庫使用者** (`CREATE USER ... WITH PASSWORD`)，資料庫可攜性更強，跨實例還原無孤立問題。

圖形化驗證映射關係

在 SSMS 中雙擊資料庫 User，`General` 頁籤顯示 `Login name`，即可確認映射。

常見誤區與正確認知

安全最佳實務

1. **優先 Windows 驗證**，禁用/重命名 `sa`，強密碼 + 鎖定策略。
2. **最小權限原則**：User 僅獲必要 Schema/物件權限，避免 `db_owner`/`db_datareader` 濫用。 3. **Schema 分離**：建立業務 Schema (`Sales`、`HR`、`Finance`)，User 綁定 `DEFAULT_SCHEMA`，物件建立於 Schema 而非 `dbo`，利於權限管理與多租戶。 4. **定期審計**：`sys.server_principals` (Login)、`sys.database_principals` (User)、`sys.server_role_members`、`sys.database_role_members`。

小結

**Login = 入場券，User = 工作證**。SQL Server 2005 強化雙層模型，明確分離「驗證」與「授權」。搞懂這層關係，才能正確設計安全架構、排查權限問題、實現最小權限原則。