別再手動給權限了！用這套 SQL 角色設計讓管理變簡單

結論#

用「自訂角色 + 內建角色」組合管理 SQL Server 權限，比直接對使用者 GRANT 更穩，也更容易維護。

適合用在哪裡#

資料庫使用者越來越多，權限開始失控
新人、廠商、支援人員需要不同層級的 DB 權限
想把權限管理做成固定模板，而不是每次手動重配
希望降低直接對 User 給權限造成的追蹤困難

流程步驟#

1. 先把角色模型定下來#

先不要急著對人給權限，先定義三種標準角色：唯讀、一般開發、可改 Schema 的進階開發。這樣做的重點不是「分很多角色」，而是把常見需求收斂成固定模板，之後新增人員只要加到對應角色就好。實務上我會把命名統一成 role_db_*，一眼就知道這是自訂角色，不會跟內建角色混在一起。

1
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'role_db_readonly')
2
    CREATE ROLE role_db_readonly;
3

4
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'role_db_developer')
5
    CREATE ROLE role_db_developer;
6

7
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'role_db_schema_developer')
8
    CREATE ROLE role_db_schema_developer;
9
GO

2. 用內建角色拼出權限模板#

建完自訂角色後，再把內建角色的能力掛進去。role_db_readonly 給查詢與檢視定義，適合報表或查詢人員；role_db_developer 再加上寫入與執行 SP 的能力，適合一般後端工程師。這樣做的好處是層級很清楚，權限差異也看得出來，不會每個人都一套特規。

1
ALTER ROLE db_datareader ADD MEMBER role_db_readonly;
2
GRANT VIEW DEFINITION TO role_db_readonly;
3
GO
4

5
ALTER ROLE db_datareader ADD MEMBER role_db_developer;
6
ALTER ROLE db_datawriter ADD MEMBER role_db_developer;
7
GRANT EXECUTE ON SCHEMA::dbo TO role_db_developer;
8
GRANT VIEW DEFINITION TO role_db_developer;
9
GO

3. 進階角色只留給真的需要的人#

role_db_schema_developer 可以再加上 db_ddladmin，讓角色具備 CREATE / ALTER / DROP 這類 DDL 能力。這類權限很方便，但也很容易手滑出事，所以不要因為「比較省事」就亂發。通常只建議給 Tech Lead、DBA，或真的需要調整物件結構的人。

1
ALTER ROLE db_datareader ADD MEMBER role_db_schema_developer;
2
ALTER ROLE db_datawriter ADD MEMBER role_db_schema_developer;
3
ALTER ROLE db_ddladmin ADD MEMBER role_db_schema_developer;
4

5
GRANT EXECUTE ON SCHEMA::dbo TO role_db_schema_developer;
6
GRANT VIEW DEFINITION TO role_db_schema_developer;
7
GO

4. 最後只管角色，不直接管人#

權限模型定好後，使用者只要加入角色即可。關鍵原則很簡單：不要直接對 User 發權限。你今天直接 GRANT SELECT TO user1，明天就很難知道這個人到底為什麼有權限；但如果是加進 role_db_readonly，整個權限來源就很清楚。權限管理本質上不是發散，而是收斂，這句話不是裝深沉，是真的能救命 =_=

1
-- 將使用者加入對應角色
2
-- ALTER ROLE role_db_readonly ADD MEMBER [user1];
3
-- ALTER ROLE role_db_developer ADD MEMBER [user2];
4
-- ALTER ROLE role_db_schema_developer ADD MEMBER [user3];
5
GO
6

7
-- 不建議：直接對使用者給權限
8
GRANT SELECT TO user1;
9

10
-- 建議：把使用者加進角色
11
ALTER ROLE role_db_readonly ADD MEMBER user1;

指令 / 範例整理#

角色與權限對照表#

角色名稱	權限內容	適用對象
role_db_readonly	查詢、檢視定義	報表、查詢、支援
role_db_developer	查詢、寫入、執行、檢視定義	後端工程師、API 開發
role_db_schema_developer	查詢、寫入、執行、DDL、檢視定義	Tech Lead、DBA

SQL Agent 補充權限#

很多人以為加了資料庫權限就能看 SQL Agent Job，結果打開一片空白，然後開始懷疑人生。這不是 SQL Server 壞掉，而是 **SQL Agent 權限在 ****msdb**，要另外補。 | 需求 | msdb 角色 | | --- | --- | | 只看自己的 Job | SQLAgentUserRole | | 看全部 Job / History | SQLAgentReaderRole | | 看全部且可操作 Job | SQLAgentOperatorRole |

1
USE msdb;
2
GO
3
ALTER ROLE SQLAgentUserRole ADD MEMBER [YourLogin];
4
GO
5

6
USE msdb;
7
GO
8
ALTER ROLE SQLAgentReaderRole ADD MEMBER [YourLogin];
9
GO
10

11
USE msdb;
12
GO
13
ALTER ROLE SQLAgentOperatorRole ADD MEMBER [YourLogin];
14
GO

補充#

GRANT EXECUTE ON SCHEMA::dbo 只涵蓋 dbo schema；若你的 SP 不在 dbo，要另外補授權。
db_ddladmin 很強，但不等於完整 DBA；它偏向物件結構調整，不是所有管理權限都包含。
若系統已經存在大量直接對 User 的授權，建議先盤點再慢慢收斂，不要一次硬切，否則很容易炸。

收尾#

把權限做成角色模板，之後維護的就不是一堆人，而是一套規則；規則穩了，系統才不會天天鬧脾氣。