绝对可行！这其实就是SQL Server角色体系设计的核心目标之一——通过角色批量管控权限，彻底告别逐个手动配置用户的麻烦。针对你的场景，我给你梳理一套最优且简便的落地方案：

既然你有3个数据库（1只读、2读写），我们可以给每个权限类别创建自定义数据库角色，再把Windows用户/AD安全组（强烈推荐用组，后续维护更省心）映射到对应角色，实现权限批量继承：

1. 创建自定义数据库角色 #

针对不同权限需求，在对应数据库执行以下T-SQL：

-- 【只读数据库】创建只读访问角色
USE [你的只读数据库名];
GO
CREATE ROLE DB_ReadOnly_Access;
-- 授予内置只读角色权限（覆盖所有表/视图的只读）
EXEC sp_addrolemember 'db_datareader', 'DB_ReadOnly_Access';
-- 如果应用需要执行只读存储过程，补充授予执行权限
GRANT EXECUTE ON SCHEMA::dbo TO DB_ReadOnly_Access;
GO

-- 【读写数据库】创建读写访问角色
USE [你的读写数据库名];
GO
CREATE ROLE DB_ReadWrite_Access;
-- 授予内置读写角色权限
EXEC sp_addrolemember 'db_datareader', 'DB_ReadWrite_Access';
EXEC sp_addrolemember 'db_datawriter', 'DB_ReadWrite_Access';
-- 授予存储过程执行权限（通常读写操作会通过存储过程实现）
GRANT EXECUTE ON SCHEMA::dbo TO DB_ReadWrite_Access;
GO

（两个读写数据库都执行上面的读写角色创建语句即可）

2. 映射AD组到角色 #

把前端应用用户所属的AD安全组（比如DOMAIN\Frontend_App_Users）添加到对应数据库的角色中，这样组内的所有用户自动继承权限：

-- 在只读数据库执行
USE [你的只读数据库名];
GO
-- 先创建数据库用户（映射AD登录）
CREATE USER [DOMAIN\Frontend_App_Users] FOR LOGIN [DOMAIN\Frontend_App_Users];
-- 添加到只读角色
EXEC sp_addrolemember 'DB_ReadOnly_Access', 'DOMAIN\Frontend_App_Users';
GO

-- 在两个读写数据库分别执行
USE [你的读写数据库名1];
GO
CREATE USER [DOMAIN\Frontend_App_Users] FOR LOGIN [DOMAIN\Frontend_App_Users];
EXEC sp_addrolemember 'DB_ReadWrite_Access', 'DOMAIN\Frontend_App_Users';
GO

USE [你的读写数据库名2];
GO
CREATE USER [DOMAIN\Frontend_App_Users] FOR LOGIN [DOMAIN\Frontend_App_Users];
EXEC sp_addrolemember 'DB_ReadWrite_Access', 'DOMAIN\Frontend_App_Users';
GO

后续新增/移除用户，只要在AD组里操作就行，完全不用修改SQL Server配置，极致省心。

应用需要调用SQL作业，得确保用户有执行作业的权限，这部分可以通过SQL Server代理的内置角色实现：

在msdb数据库（SQL Server代理的系统数据库）中，把AD组添加到SQLAgentUserRole角色——这个角色允许用户查看、执行自己拥有的作业：

USE msdb;
GO
CREATE USER [DOMAIN\Frontend_App_Users] FOR LOGIN [DOMAIN\Frontend_App_Users];
EXEC sp_addrolemember 'SQLAgentUserRole', 'DOMAIN\Frontend_App_Users';
GO

另外，作业步骤本身的操作（比如修改数据、执行存储过程）需要的数据库权限，已经通过前面的数据库角色覆盖了，只要作业是用当前用户上下文执行，就会自动继承权限。

1. 优先用AD组而非单个用户：这是批量管理的核心，避免后续逐个用户配置的重复劳动。
2. 最小权限原则：给角色只授予必要的权限，比如只读角色不要加写权限，作业权限只给SQLAgentUserRole（不要给更高权限的SQLAgentOperatorRole或SQLAgentAdminRole）。
3. SQL Server 2008 R2完全支持：这套方案用到的角色创建、sp_addrolemember、AD登录映射等功能，在2008 R2里都是成熟可用的，没有版本兼容问题。

内容的提问来源于stack exchange，提问作者user8165942