萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 建立登入帳號與使用者對應完整步驟

前置檢查:混合模式驗證

  1. SSMS → 右鍵伺服器根節點 → 屬性 → 安全性
  2. 伺服器驗證:選 SQL Server 和 Windows 驗證模式
  3. 確定 → 重新啟動 SQL Server 服務 (右鍵伺服器 → 重新啟動)

建立登入 (伺服器層級)

  1. 展開 安全性 → 右鍵 登入 → 新增登入
  2. 一般 頁籤:
    • 登入名稱:app_user
    • 選 SQL Server 驗證
    • 密碼:輸入強密碼,取消勾選「使用者必須在下次登入時變更密碼」
    • 預設資料庫:選目標資料庫 (如 MyDB)
    • 預設語言:中文 (繁體) 或 English
  3. 伺服器角色 頁籤:通常保持 public,除非需 sysadmin 等高權限
  4. 使用者對應 頁籤 (關鍵):
    • 勾選目標資料庫 (如 MyDB)
    • 下方「資料庫角色成員資格」勾選:db_datareader、db_datawriter、db_ddladmin (視需求)
    • 或勾選 db_owner (完整控制,謹慎授予)
  5. 狀態 頁籤:
    • 「授與連接到資料庫引擎」→ 啟用
    • 「登入」→ 已啟用
  6. 確定建立

T-SQL 腳本等效操作 (建議納入版控)

-- 1. 建立登入
CREATE LOGIN [app_user] WITH PASSWORD = 'StrongP@ssw0rd!', 
    DEFAULT_DATABASE = [MyDB], 
    DEFAULT_LANGUAGE = [us_english],
    CHECK_EXPIRATION = OFF, CHECK_POLICY = ON;

-- 2. 建立資料庫使用者並對應登入
USE [MyDB];
CREATE USER [app_user] FOR LOGIN [app_user];

-- 3. 授與角色
ALTER ROLE [db_datareader] ADD MEMBER [app_user];
ALTER ROLE [db_datawriter] ADD MEMBER [app_user];
-- ALTER ROLE [db_owner] ADD MEMBER [app_user];  -- 謹慎使用

-- 4. 權限精細控制 (範例)
GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO [app_user];
DENY DELETE ON SCHEMA::dbo TO [app_user];

常見問題排查

現象可能原因解決
登入失敗 18456密碼錯誤/登入停用/非混合模式檢查狀態頁籤、錯誤日誌狀態碼
登入成功但查無資料表未在使用者對應勾選資料庫/角色檢查使用者對應頁籤
權限不足角色/權限未授與授與 db_datareader 等或物件級 GRANT

安全最佳實務

  • 原則:最小權限、定期換密、稽核登入失敗
  • 應用程式帳號:僅授 db_datareader/writer + 執行儲存程序權限
  • 開發人員帳號:獨立登入、非 sysadmin、透過 AD 群組管理
  • 停用/刪除離職人員帳號流程自動化

總結

登入 (Server-level) 與使用者對應 是 SQL Server 安全模型的核心。圖形介面適合單次操作,T-SQL 腳本適合自動化部署與版本控制。嚴格遵循最小權限原則,建立完整權限矩陣文件。

copyright © 萬盛學電腦網 all rights reserved