Just putting this here for future reference.
When using a temporary installation account to build BizTalk environments the SQL Agent Job owner becomes that temporary account. Which is fine until that temporary account is deleted at the end of the project and all the jobs stop working. (Saw a client recently whose jobs hadn’t been running for 3 months).
Anyway going in after installation and manually changing the owner is a pain so I created this script.
Run this:
USE [MSDB]
GO
SELECT 'EXEC msdb.dbo.sp_update_job @job_name=N'''+NAME+''' , @owner_login_name=N''sa''' FROM msdb.dbo.sysjobs where name like '%biztalk%'
Then copy and paste the output and run that. Here’s what I ran…
EXEC msdb.dbo.sp_update_job @job_name=N'Backup BizTalk Server (BizTalkMgmtDb)' , @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'CleanupBTFExpiredEntriesJob_BizTalkMgmtDb' , @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'DTA Purge and Archive (BizTalkDTADb)' , @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb' , @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'MessageBox_Message_Cleanup_BizTalkMsgBoxDb' , @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb' , @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'MessageBox_Parts_Cleanup_BizTalkMsgBoxDb' , @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'MessageBox_UpdateStats_BizTalkMsgBoxDb' , @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'Monitor BizTalk Server (BizTalkMgmtDb)' , @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb' , @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'PurgeSubscriptionsJob_BizTalkMsgBoxDb' , @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'Rules_Database_Cleanup_BizTalkRuleEngineDb' , @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'TrackedMessages_Copy_BizTalkMsgBoxDb' , @owner_login_name=N'sa'