1. Define the Role Model First#

• Don’t rush to grant permissions to individuals. First, define three standard roles: read-only, general developer, and advanced developer (who can modify schema). The key here isn’t to “create many roles,” but to consolidate common requirements into fixed templates. Afterwards, new personnel only need to be added to the corresponding role. In practice, I unify the naming convention to role_db_* so it’s immediately clear these are custom roles and won’t be confused with built-in roles.

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. Use Built-in Roles to Assemble Permission Templates#

• After creating custom roles, assign the capabilities of built-in roles to them. role_db_readonly grants query and view definition permissions, suitable for reporting or query personnel. role_db_developer adds write capabilities and the ability to execute SPs, suitable for general backend engineers. The advantage of this approach is clear layering, visible permission differences, and avoiding unique, custom permissions for everyone.

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. Reserve Advanced Roles for Those Who Truly Need Them#

• role_db_schema_developer can be further enhanced with db_ddladmin, giving the role DDL capabilities like CREATE / ALTER / DROP. While these permissions are convenient, they can also easily lead to accidental issues. Therefore, do not grant them indiscriminately just because it’s “easier.” It’s generally recommended only for Tech Leads, DBAs, or individuals who genuinely need to adjust object structures.

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. Finally, Manage Roles, Not Individuals Directly#

• Once the permission model is established, users only need to be added to the appropriate role. The key principle is simple: do not grant permissions directly to users. If you directly GRANT SELECT TO user1 today, it will be difficult to understand why this person has that permission tomorrow. However, if they are added to role_db_readonly, the entire source of the permission becomes clear. Permission management is essentially about convergence, not divergence. This isn’t just a profound statement; it can genuinely save you trouble =_=

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 and Permission Mapping Table#

SQL Agent Additional Permissions#

• Many people assume that granting database permissions allows them to view SQL Agent Jobs, only to find a blank screen and start questioning everything. This isn’t SQL Server being broken; it’s because SQL Agent permissions are in msdb and need to be granted separately.

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