主页 / server / 问题 / 31885
Accepted
Ed Leighton-Dick
Asked: 2009-06-26 13:28:37 +0800 CST

从存储过程调用 sp_start_job

  • 772

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

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

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 中执行此操作的最佳方法吗？

sql-server sql-server-2005 sql

5 个回答

  1. 很高兴您解决了这个问题，但所有权链接不是推荐的解决方案。由于您似乎确实担心所涉及权利的安全性和适当的粒度，因此我将添加此回复，尽管迟了，作为对正在发生的事情以及如何解决此问题的参考。

    EXECUTE AS 模拟范围

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

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

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

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

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

    使数据库范围的模拟能够访问通常不允许的资源，模拟上下文的身份验证器必须被信任。对于数据库范围的模拟，身份验证器是数据库 dbo。这可以通过两种可能的方式来实现：

    • 通过打开对模拟上下文进行身份验证的数据库（即发出 EXECUTE AS 子句的数据库）上的 TRUSTWORTHY 属性。
    • 通过使用代码签名。

    这些详细信息在 MSDN：Extended Database Impersonation by Using EXECUTE AS中进行了描述。

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

    • 在应用程序数据库中创建自签名证书
    • dbo.StartAgentJob用这个证书签名
    • 删除证书的私钥
    • 将证书导出到磁盘
    • 将证书导入msdb
    • 从导入的证书创建派生用户msdb
    • 向派生用户授予 AUTHENTICATE 权限msdb

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

    1. 映射模拟用户agentProxy用户msdb并授予他执行权限msdb.dbo.sp_start_job
    2. 授予msdb验证者证书派生用户执行权限
    3. 向过程添加新签名，为其派生一个用户msdb并将执行权限授予该派生用户

    方案1.简单，但有一个很大的缺点：agentProxy用户现在可以随意执行msdb.dbo.sp_start_job，他真正被授予访问权限msdb并具有执行权限。

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

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

    下面是对应的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 子句）：

    顺便说一句，如果你想测试我的脚本并且你住在东半球，或者在英国夏令时，一定要阅读我在测试之前链接的最后一篇文章。

    • 9
  2. Best Answer

    您是否将 agentProxy 登录名放入 msdb 数据库并授予它运行 sp_start_job 的权限？如果没有，您需要为 msdb 数据库和您的用户数据库启用数据库权限链接。

    您最好将登录名放入 msdb 数据库并授予它正确的权限。

    • 5

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

    http://www.stackoverflow.com

    • 0

  4. 检查网络 SQLAgentOperatorRole 上的随机 SQL 实例不会直接为您提供 sp_start_job 权限，它从 SQLAgentUserRole 继承它们。

    使用以下命令仔细检查：

    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 中运行它并仔细检查您没有继承任何显式拒绝访问。

    hth。

    • 0

  5. 在不授予额外权限的情况下实现此目的的一种方法：不要让存储过程直接启动作业，而只允许存储过程在表中翻转一点（在应用程序数据库中）；然后，让作业每分钟左右运行一次，检查位是否翻转，如果是，则执行工作并再次翻转位。如果作业发现该位未翻转，则作业将退出。

    如果您不介意延迟（并且工作经常运行），它就像一个魅力。

    • 0

相关问题