如何在SQL Server中同时激活两个数据库的应用角色？

前置配置：锁死Windows用户权限 #

先确保你的Windows用户在两个数据库中仅保留默认的public权限，拒绝所有其他显式权限——这样用户自身无法执行任何数据库操作，必须依赖应用角色。

分别为prod和common创建应用角色并分配必要权限：

-- 配置prod数据库应用角色
USE prod;
CREATE APPLICATION ROLE prod_app_role WITH PASSWORD = 'YourStrongProdPass!';
-- 按需分配权限，例如：
GRANT SELECT, INSERT, UPDATE ON dbo.prod_data TO prod_app_role;

-- 配置common数据库应用角色
USE common;
CREATE APPLICATION ROLE common_app_role WITH PASSWORD = 'YourStrongCommonPass!';
-- 按需分配权限，例如：
GRANT SELECT ON dbo.shared_lookup TO common_app_role;

核心解决方案：绕过单应用角色限制 #

SQL Server默认一个连接只能激活一个应用角色，直接先后激活会覆盖前一个的安全上下文。以下两种方法可实现双角色权限的同时使用：

方法1：存储过程代理（安全优先） #

通过在common库创建带EXECUTE AS的存储过程，让激活后的prod_app_role可以调用该过程执行common库的操作：

1. 在common库创建代理存储过程：

USE common;
CREATE PROCEDURE dbo.ExecuteAsCommonRole
    @SqlStmt NVARCHAR(MAX)
WITH EXECUTE AS 'common_app_role'
AS
BEGIN
    SET NOCOUNT ON;
    EXEC sp_executesql @SqlStmt;
END;

2. 给prod_app_role授权调用该过程：

USE common;
GRANT EXECUTE ON dbo.ExecuteAsCommonRole TO [prod].[dbo].[prod_app_role];

3. 在你的.exe应用中执行逻辑：

-- 第一步：激活prod应用角色（连接默认指向prod库时执行）
EXEC sp_setapprole 'prod_app_role', 'YourStrongProdPass!';

-- 直接用prod_app_role权限操作prod库
SELECT * FROM dbo.prod_data;

-- 通过代理过程用common_app_role权限操作common库
EXEC common.dbo.ExecuteAsCommonRole N'SELECT * FROM dbo.shared_lookup';

方法2：跨数据库信任（简化操作，适用于SQL Server 2016+） #

如果你的SQL Server版本≥2016，可以启用跨数据库信任，让prod_app_role直接获得common库的权限，无需额外激活角色：

1. 开启数据库信任属性：

ALTER DATABASE prod SET TRUSTWORTHY ON;
ALTER DATABASE common SET TRUSTWORTHY ON;

2. 在common库中将权限授予prod_app_role：

USE common;
GRANT SELECT ON dbo.shared_lookup TO [prod].[dbo].[prod_app_role];

3. 应用中只需激活一次prod_app_role，即可同时操作两个库：

EXEC sp_setapprole 'prod_app_role', 'YourStrongProdPass!';

-- 操作prod库
INSERT INTO dbo.prod_data (col1) VALUES ('test');

-- 直接操作common库（权限已通过跨数据库授权获得）
SELECT * FROM common.dbo.shared_lookup;

关键注意事项 #

• 优先使用方法1，因为TRUSTWORTHY ON会提升数据库的安全风险，方法1的代理模式更可控。
• 应用角色的密码必须加密存储，绝对不能硬编码在.exe中。
• 测试验证：用Windows用户单独连接数据库，确保无法执行任何读写操作，只有通过应用角色才能正常访问。

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