The purpose of this script is to set a proxy on each job step to allow the owner of the job to not have access to the target database. This applies when a separate server is used for ETL and data storage. This also has a strong effect on the performance of the database server as significantly less memory has to be allocated to the Integration Services service.
This assume you can segregate the jobs by job category to make sure only the targeted step are changed. This could also be done easily by job owner.
USE MSDB
GO
SELECT * FROM sysproxies;
--Use this to restrict the jobs to only the categories you want to change
DECLARE @categoryMatch nvarchar(30) = 'test_%';
--Set the proxy value to the values desired in the above query
DECLARE @proxy_value nvarchar(10) = 2;
DECLARE @CurJobId nvarchar(50);
DECLARE @curStepId nvarchar(50);
SELECT sj.job_id, sjs.step_id, proxy_id
FROM sysjobs sj
INNER JOIN sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE category_id IN ( select category_id FROM syscategories WHERE name like @categoryMatch)
AND (sjs.proxy_id != @proxy_value OR proxy_id IS NULL);
DECLARE db_cursor CURSOR FOR
SELECT sj.job_id, sjs.step_id
FROM sysjobs sj
INNER JOIN sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE category_id IN (SELECT category_id FROM syscategories WHERE name like @categoryMatch)
AND (sjs.proxy_id != @proxy_value OR proxy_id IS NULL);
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @CurJobId, @curStepId
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Job_ID: ' + @curJobId + ' Job Step: ' + @curStepId;
EXEC dbo.sp_update_jobstep
@job_id = @curJobId
,@step_id = @curStepId
,@proxy_id = @proxy_value;
FETCH NEXT FROM db_cursor INTO @CurJobId, @curStepId
END
CLOSE db_cursor
DEALLOCATE db_cursor
