從儲存過程呼叫 sp_start_job

我們的開發人員需要能夠從他們的 .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 中執行此操作的最佳方法嗎？

您是否將 agentProxy 登錄名放入 msdb 數據庫並授予它執行 sp_start_job 的權限？如果沒有，您需要為 msdb 數據庫和您的使用者數據庫啟用數據庫權限連結。

您最好將登錄名放入 msdb 數據庫並授予它正確的權限。

很高興您解決了這個問題，但所有權連結不是推薦的解決方案。由於您似乎確實擔心所涉及權利的安全性和適當的粒度，因此我將添加此回复，儘管遲了，作為對正在發生的事情以及如何解決此問題的參考。

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 子句）：

• 簽署啟動的程序
• 從啟動的過程中呼叫另一個數據庫中的過程
• 為什麼功能……在啟動狀態下不起作用？
• 證書尚未生效

順便說一句，如果你想測試我的腳本並且你住在東半球，或者在英國夏令時，一定要閱讀我在測試之前連結的最後一篇文章。

引用自：https://serverfault.com/questions/31885