从存储过程调用sp_start_job

我们的开发人员需要能够从.Net代码启动SQL Server代理作业。我知道我可以调用msdb..sp_start_job来做到这一点，但我不想给一般用户帐户直接访问运行作业。

我想要做的是使用WITH EXECUTE AS子句在应用程序的数据库中创build一个存储过程来模拟代理帐户。我们拥有的程序是：

CREATE PROCEDURE dbo.StartAgentJob WITH EXECUTE AS 'agentProxy' AS BEGIN EXEC msdb.dbo.sp_start_job N'RunThisJob'; END

当我们运行这个，但是，我们得到以下消息：

 The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

有任何想法吗？这甚至是在SQL2005中做到这一点的最好方法吗？

您是否已将agentProxylogin名置于msdb数据库中，并赋予其运行sp_start_job的权限？如果不是，则需要为msdb数据库和用户数据库启用数据库权限链接。

你可能最好把login到msdb数据库，并授予它正确的权利。

我很高兴你解决了这个问题，但所有权链不是推荐的解决scheme。由于您似乎对安全性和涉及的权限的合理粒度感到十分担忧，所以我将这个答复作为参考，尽pipe已经很晚了，但是如何解决这个问题。

EXECUTE作为模拟范围

EXECUTE AS子句有两种types：EXECUTE AS LOGIN和EXECUTE AS USER。 EXECUTE AS LOGIN由服务器进行身份validation，并且是整个SQL实例（服务器范围）所信任的模拟上下文：

使用EXECUTE AS LOGIN语句或使用EXECUTE AS子句在服务器范围的模块中模拟主体时，模拟的范围在服务器范围内。这意味着在上下文切换之后，可以访问模拟login具有权限的服务器内的任何资源。

EXECUTE AS USER由数据库进行身份validation，并且是仅受该数据库（数据库范围）信任的模拟上下文：

但是，使用EXECUTE AS USER语句或使用EXECUTE AS子句在数据库范围模块中模拟主体时，默认情况下，模拟的范围仅限于数据库。这意味着对数据库范围之外的对象的引用将返回一个错误。

具有EXECUTE AS子句的存储过程将创build数据库作用域模拟上下文，因此无法在数据库之外引用对象，因为您将无法引用msdb.dbo.sp_start_job因为它位于msdb 。还有很多其他示例可用，例如尝试访问服务器作用域DMV，试图使用链接的服务器或尝试将Service Broker消息传递到另一个数据库。

启用数据库作用域模拟来访问通常不被允许的模拟上下文的身份validation者的资源必须是可信的。对于数据库作用域模拟，authentication者是数据库dbo。这可以通过两种可能的方式来实现：

通过打开validation模拟上下文的数据库（即发出EXECUTE AS子句的数据库）上的TRUSTWORTHY属性。
通过使用代码签名。

这些细节在MSDN中描述：使用EXECUTE AS扩展数据库模拟。

当您通过跨数据库所有权链来解决问题时，您已经在整个服务器级别启用了跨数据库链接，这被认为是安全风险。实现期望结果的最受控制的细粒度方法是使用代码签名：

在应用程序数据库中创build一个自签名证书
使用此证书签署dbo.StartAgentJob
放弃证书的私钥
将证书导出到磁盘
将证书导入msdb
从msdb导入的证书创build派生用户
向msdb的派生用户授予AUTHENTICATE权限

这些步骤确保现在可以在msdb信任dbo.StartAgentJob过程的EXECUTE AS上下文，因为上下文由在msdb中具有AUTHENTICATE权限的主体签名。这解决了一半的难题。另一半是实际上将msdb.dbo.sp_start_job上的EXECUTE权限授予现在可信的模拟上下文。有几种方法可以做到这一点：

在msdb映射模拟的用户agentProxy用户，并授予他对msdb.dbo.sp_start_job执行权限
授予msdbauthentication者证书派生用户的执行权限
在过程中添加一个新的签名，在msdb为它派生一个用户，并向这个派生用户授予执行权限

选项1很简单，但有一个很大的缺点： agentProxy用户现在可以自行执行msdb.dbo.sp_start_job ，他真的被授予访问msdb的权限。

选项3是正确的，但我觉得是没有必要的矫枉过正。

所以我的select是选项2：将msdb.dbo.sp_start_job上的EXECUTE权限授予在msdb创build的证书派生用户。

这里是相应的SQL：

 use [<appdb>]; go create certificate agentProxy ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y' with subject = 'agentProxy' , start_date='01/01/2009'; go ADD SIGNATURE TO OBJECT::[StartAgentJob] BY CERTIFICATE [agentProxy] WITH PASSWORD = 'pGFD4bb925DGvbd2439587y'; go alter certificate [agentProxy] remove private key; go backup certificate [agentProxy] to file='c:\temp\agentProxy.cer'; go use msdb go create certificate [agentProxy] from file='c:\temp\agentProxy.cer'; go create user [agentProxyAuthenticator] from certificate [agentProxy]; go grant authenticate to [agentProxyAuthenticator]; grant execute on msdb.dbo.sp_start_job to [agentProxyAuthenticator]; go use [<appdb>]; go exec dbo.StartAgentJob; go

我的博客有一些关于这个主题的文章，是在Service Broker激活的过程中编写的（因为它们需要一个EXECUTE AS子句）：

签署激活的程序
从激活的过程中调用另一个数据库中的过程
为什么function…不能在激活状态下工作？
证书尚未生效

顺便说一句，如果你试图testing我的脚本，你住在东半球或英国的夏季时间，那么肯定读了我testing之前链接的最后一篇文章。

由于您试图从.NET代码启动SQL Server代理，这对于StackOverflow来说可能是一个更好的问题？

http://www.stackoverflow.com

检查networking上的随机SQL实例SQLAgentOperatorRole不会直接为您提供sp_start_job权限，而是从SQLAgentUserRoleinheritance它们。

仔细检查使用：

 select dp.NAME AS principal_name, dp.type_desc AS principal_type_desc, o.NAME AS object_name, p.permission_name, p.state_desc AS permission_state_desc from sys.database_permissions p left OUTER JOIN sys.all_objects o on p.major_id = o.OBJECT_ID inner JOIN sys.database_principals dp on p.grantee_principal_id = dp.principal_id where o.name = 'sp_start_job'

在MSDB中运行这个，仔细检查你是否inheritance了任何明确的拒绝访问。

心连心。

实现这一点的一种方法是不授予额外的权限：不要让存储过程直接启动作业，而只是允许存储的过程在表中翻转一点（在应用程序数据库中）。那么，让工作每分钟左右运行一次，检查一下位是否翻转，如果是的话，执行工作，再把这个位翻转回来。如果工作看到这个位没有被翻转，工作就会退出。

如果你不介意延迟（和工作频繁），就像魅力一样。