证书签名存储过程未按预期执行：TSQL作业启停权限问题

问题背景 #

编写存储过程包装器用于让用户启用/禁用特定TSQL作业，通过证书签名存储过程规避权限提升风险。预期只需授予用户存储过程执行权限，证书用户拥有SQLAgentOperatorRole角色即可调用sp_update_job，但存储过程签名后仍以调用者身份执行，需排查解决。

测试代码 #

USE [msdb]
GO

DECLARE @jobId BINARY(16)

EXEC  msdb.dbo.sp_add_job @job_name=N'TestJob', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=2, 
@notify_level_page=2, 
@delete_level=0, 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
GO

IF NOT EXISTS (SELECT * FROM sys.certificates where NAME = 'JobExecTestCert')
BEGIN
    CREATE CERTIFICATE [JobExecTestCert]
    ENCRYPTION BY PASSWORD = N'Password1'
    WITH SUBJECT = N'Certificate for executing jobs wrapper'
END

IF NOT EXISTS (SELECT * FROM SYS.server_principals WHERE NAME = 'JobExecTest' AND TYPE = 'C')
BEGIN
    CREATE USER JobExecTest FROM CERTIFICATE JobExecTestCert
    ALTER ROLE SQLAgentOperatorRole ADD MEMBER JobExecTest
END

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE type = 'R' AND name = 'TestJob_Exec')
BEGIN
    CREATE ROLE TestJob_Exec
END
GO

USE msdb
GO

CREATE OR ALTER PROCEDURE [dbo].[usp_EnableDisableTestJob] @enabled CHAR(1) AS

BEGIN
DECLARE @JobName sysname,
@enabledflag TINYINT,
@jobid UNIQUEIDENTIFIER;

SELECT @enabledflag = CASE @enabled
WHEN 'Y' THEN 1
WHEN 'N' THEN 0
ELSE 1
END 

SELECT SYSTEM_USER 'system Login'  
   , USER AS 'Database Login'  
   , NAME AS 'Context'  
   , TYPE  
   , USAGE   
   FROM sys.user_token  



EXEC [sp_update_job] @job_name = 'TestJob', @enabled = @enabledflag 

END
GO


ADD SIGNATURE
TO [dbo].[usp_EnableDisableTestJob]
BY CERTIFICATE [JobExecTestCert]
WITH PASSWORD = 'Password1';

GRANT EXECUTE ON [dbo].[usp_EnableDisableTestJob] TO [TestJob_Exec]
GO

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'TestUsr')
BEGIN
CREATE LOGIN TestUsr WITH PASSWORD = 'Password1'
END

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'TestUsr')
BEGIN
CREATE USER TestUsr FROM LOGIN TestUsr
ALTER ROLE [TestJob_Exec] ADD MEMBER TestUsr
END


EXECUTE AS LOGIN = 'TestUsr';  
GO  
[dbo].[usp_EnableDisableTestJob] 
   @enabled = 'Y'
   GO

SELECT SUSER_NAME()

问题根源与修正步骤 #

1. 缺少证书到服务器登录名的映射 #

仅创建数据库用户不足以让证书身份在执行sp_update_job时生效，因为该存储过程内部会验证服务器级权限上下文。需要将证书映射为服务器登录名：

USE master
GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE NAME = 'JobExecTestLogin' AND TYPE = 'C')
BEGIN
    CREATE LOGIN JobExecTestLogin FROM CERTIFICATE msdb.dbo.JobExecTestCert
END
GO

2. 确保证书用户的角色权限生效 #

确认msdb中的证书用户JobExecTest已正确加入SQLAgentOperatorRole：

USE msdb
GO
ALTER ROLE SQLAgentOperatorRole ADD MEMBER JobExecTest

3. 验证签名与执行上下文 #

执行存储过程后，查询sys.user_token应包含类型为CERTIFICATE的JobExecTest身份，说明签名已生效。此时sp_update_job会使用证书用户的权限执行，而非调用者的权限。

4. 强化权限控制（可选） #

为避免用户篡改作业名，可在存储过程中硬编码目标作业名（如已实现的TestJob），进一步缩小权限范围。

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