SQL Server 建立使用者與授權完整指南：登入、資料庫使用者、權限授予 (GRANT) 與角色管理

前言：三層安全架構

SQL Server 安全模型分三層：

1. 伺服器層級登入：CREATE LOGIN / sp_addlogin，決定「能否連線到伺服器」。
2. 資料庫層級使用者：CREATE USER / sp_grantdbaccess，將登入映射到資料庫，決定「能進哪個資料庫」。
3. 物件層級權限：GRANT / DENY / REVOKE，決定「在資料庫裡能做什麼」。

一、建立登入 (伺服器層級)

現代語法 (SQL 2005+)

CREATE LOGIN [AppUser] WITH PASSWORD = 'StrongP@ssw0rd!', DEFAULT_DATABASE = [AppDB];
-- 或對應 Windows 帳戶
CREATE LOGIN [DOMAIN\AppUser] FROM WINDOWS;

舊版相容 (sp_addlogin，已棄用但仍支援)

EXEC sp_addlogin 'AppUser', 'StrongP@ssw0rd!', 'AppDB';

二、建立資料庫使用者 (資料庫層級)

現代語法

USE AppDB;
CREATE USER [AppUser] FOR LOGIN [AppUser];

舊版相容

USE AppDB;
EXEC sp_grantdbaccess 'AppUser';

三、授予物件權限 (GRANT)

語法結構

GRANT 權限清單 ON 物件 TO 受體;
-- 權限清單：SELECT, INSERT, UPDATE, DELETE, REFERENCES, EXECUTE, VIEW DEFINITION 等
-- 物件：TABLE, VIEW, PROCEDURE, FUNCTION, SYNONYM, SCHEMA 等
-- 受體：USER, ROLE, PUBLIC

實務範例

USE AppDB;

-- 1. 授予單一資料表 SELECT, UPDATE
GRANT SELECT, UPDATE ON dbo.Orders TO AppUser;

-- 2. 授予結構描述層級權限 (一次套用該結構描述所有現有/未來物件)
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO AppUser;

-- 3. 授予預存程序執行權限
GRANT EXECUTE ON dbo.usp_GetOrderDetails TO AppUser;

-- 4. 授予角色 (再將使用者加入角色)
CREATE ROLE AppReadOnly;
GRANT SELECT ON SCHEMA::dbo TO AppReadOnly;
ALTER ROLE AppReadOnly ADD MEMBER AppUser;

-- 5. 撤銷權限
REVOKE UPDATE ON dbo.Orders FROM AppUser;
-- 明確拒絕 (優先於 GRANT)
DENY DELETE ON dbo.Orders TO AppUser;

四、固定資料庫角色快速授權

加入角色語法

ALTER ROLE db_datareader ADD MEMBER AppUser;  -- SQL 2012+
-- 舊版
EXEC sp_addrolemember 'db_datareader', 'AppUser';

五、伺服器層級固定角色

六、權限稽核查詢腳本

-- 1. 列出資料庫使用者與角色成員
SELECT
    dp.name AS UserName,
    dp.type_desc AS UserType,
    r.name AS RoleName
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
LEFT JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
WHERE dp.type IN ('S','U','G')  -- SQL, Windows, Group
ORDER BY dp.name, r.name;

-- 2. 列出物件層級權限
SELECT
    dp.name AS Grantee,
    obj.name AS ObjectName,
    perm.permission_name,
    perm.state_desc  -- GRANT / DENY
FROM sys.database_permissions perm
JOIN sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id
JOIN sys.objects obj ON perm.major_id = obj.object_id
WHERE perm.class = 1  -- OBJECT_OR_COLUMN
ORDER BY dp.name, obj.name;

-- 3. 檢查是否誤加入 db_denydatareader
SELECT dp.name
FROM sys.database_principals dp
JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
WHERE r.name = 'db_denydatareader';

結語

建立使用者與授權的標準流程：CREATE LOGIN → CREATE USER → GRANT 最小權限 (物件/結構描述層級優先) → 必要時加入固定角色。避免使用 db_denydatareader/writer、避免直接授予 db_owner/sysadmin 給應用程式。定期執行權限稽核腳本，確保最小權限原則落實。