SQL Server 使用者、角色與權限管理完整指南

SQL Server 採雙層安全架構：伺服器層級「登入」與資料庫層級「使用者」。本文系統說明三類特殊使用者、建立登入/使用者/角色的 T-SQL 指令、權限授予 (GRANT/DENY/REVOKE) 與實務範例。

核心安全主體

三類特殊使用者權限

• 系統管理員 (sysadmin)：伺服器完全控制權，可執行任何操作，跨越所有權限檢查。
• 資料庫擁有者 (dbo)：建立資料庫者自動成為 dbo，擁有該資料庫所有物件的完全權限，可授權他人。
• 一般使用者：僅擁有被明確授予 (GRANT) 或透過角色繼承的權限。

建立登入與使用者 (T-SQL)

-- 1. 建立 SQL 登入
EXEC sp_addlogin @loginame = 'zhang', @passwd = 'zhangling', @defdb = 'master', @deflanguage = 'us_english', @fullname = 'Zhang Ling';
-- 現代寫法 (SQL 2005+)
CREATE LOGIN zhang WITH PASSWORD = 'zhangling', DEFAULT_DATABASE = master, DEFAULT_LANGUAGE = us_english;

-- 2. 在資料庫建立使用者並加入角色
USE tele114; GO
EXEC sp_addgroup 'china';  -- 建立使用者群組 (舊版角色)
EXEC sp_adduser @loginame = 'zhang', @name_in_db = 'zhang1', @grpname = 'china';
-- 現代寫法
CREATE USER zhang1 FOR LOGIN zhang;
EXEC sp_addrolemember 'db_datareader', 'zhang1';
EXEC sp_addrolemember 'db_datawriter', 'zhang1';

權限授予體系 (GRANT/DENY/REVOKE)

實務授權範例

USE tele114; GO
-- 授予 zhang1 建表、建檢視、建預存程序權限
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO zhang1;

-- 授予 zhang1 對 students 表的 SELECT、REFERENCES 權限，並允許轉授
GRANT SELECT, REFERENCES ON students TO zhang1 WITH GRANT OPTION;

-- 授予 zhang1 對 students 表特定欄位的 UPDATE 權限
GRANT UPDATE ON students (name, native) TO zhang1;

-- 查看有效權限
EXEC sp_helprotect;

清除使用者與登入 (正確順序)

USE tele114; GO
EXEC sp_dropuser 'zhang1';      -- 1. 刪除資料庫使用者
EXEC sp_dropgroup 'china';      -- 2. 刪除角色/群組
USE master; GO
EXEC sp_droplogin 'zhang';      -- 3. 刪除伺服器登入

現代最佳實務 (SQL 2005+)

• 使用 CREATE LOGIN / CREATE USER / CREATE ROLE 取代 sp_addlogin / sp_adduser / sp_addgroup。
• 採用角色基礎存取控制 (RBAC)：建立應用程式角色 (如 app_readonly, app_readwrite)，將權限授予角色，再將使用者加入角色。
• 善用結構描述 隔離物件擁有權：CREATE SCHEMA app AUTHORIZATION dbo，物件建立於結構描述下而非直接屬於使用者。
• 定期稽核：sys.server_permissions, sys.database_permissions, fn_my_permissions。

遵循「最小權限原則」，結合角色與結構描述管理，能建構安全、可維護的資料庫權限體系。