RiCo技術農場

昨天有一個SQL Agent Job 執行cmdexec作業發生錯誤，

因為 Job Owner 非sysadmin，

但是使用者又不希望給予Job Owner 該權限，

這時後就可以利用SQL Agent Proxy來解決，

自己將整各過程記錄下來，方便以後年紀大時有所參考。

 

SQL Agent Job Error

該作業沒有 cmdshell execute權限。

 

流程示意圖

1.指定Job 執行身分為SQL Agent Proxy

2.SQL Agent Proxy 對應相關認證

3.藉由認證指定特定Windows User(該User有足夠權限)執行該Job。

 

Note:權限優先權 =Job Owner > SQL Agent Account

 

開始進行整各流程

1.先建立認證(Credential)

這裡我選用Administrator 因為該用戶屬於sysadmin角色。

 

TSQL

CREATE CREDENTIAL MyCredential WITH IDENTITY = N'domain\Administrator', SECRET = N'mypassword'

2.建立SQL Agent Proxy 

選擇前面建立的認證名稱，子系統選擇作業系統(該job是刪除OS檔案作業)。

SQLAgentUserRole(只對本機作業、操作員和作業排程有權限，算是最小權限的角色) 。

grant test to Proxy。

TSQL

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'Myproxy',@credential_name=N'MyCredential', 
        @enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'Myproxy', @subsystem_id=3
GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'Myproxy', @login_name=N'test'
GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'Myproxy', @msdb_role=N'SQLAgentUserRole'
GO

3.修改Agent Job 執行身分

TSQL

USE [msdb]
GO
EXEC msdb.dbo.sp_update_jobstep @job_id=N'2a30fb24-dc94-462c-9bfd-1a7f9c0eeeef', @step_id=1 , 
        @proxy_name=N'Myproxy'
GO

 

再次執行Job

可以看到執行身分為Administrator，且Job也成功了。

 

 

參考

SQL Server Agent 固定資料庫角色

SQL Server Agent 管理的安全性