0

I have about 300+ jobs in SQL Server 2005, and most of those jobs have a "Runas" under different user other than me. Please help with a query where I could do a bulk update to change the "Runas" from the old user to me? I don't want to go through each job and double clicking on each of the stepid and change the "Runas" user, please help, thanks.

TL

Chains
  • 12,541
  • 8
  • 45
  • 62
user3083228
  • 3
  • 2
  • If you double click on the job then click on steps then double click on one of the stepID...there would be a pop-up, in the pop up it would have Step name, Type, Run as: ...etc. In the run as field it is populated with an old user and i want to change it to myself, but I want to do it through query so that it can also update all the other jobs – user3083228 Jan 03 '14 at 17:06

1 Answers1

1

I am unable to test this in SQL Server 2005 (the below works in 2008 and 2012). According to technet, the same syntax is applicable to 2005.

As you may not want to update jobs that are related to maintenance tasks, I feel the below steps are the best way to do this. Please note that this will only change steps that do not require a system account to run, i.e., it will not update PowerShell steps and similar.

Make sure you run all the below in your MSDB database as that is where all your jobs are held in SQL Server. Run the following select statement (replace the UserName with the user you want to use, if using a domain/network name prefix with domain\username):

SELECT 'EXEC sp_update_jobstep @job_name = ''' + sj.name + ''', @step_id = ' + 
Convert(VarChar,sjs.step_id) + ', @database_user_name = ''UserName''' AS OutputStatements
FROM dbo.sysjobsteps sjs
INNER JOIN dbo.sysjobs sj
ON sjs.job_id = sj.job_id

This will output something similar to this in your results window:

EXEC sp_update_jobstep @job_name = 'SQL MDW: Auto Index Management', @step_id = 1, @database_user_name = 'UserName'
EXEC sp_update_jobstep @job_name = 'SQL MDW: Auto Index Management', @step_id = 2, @database_user_name = 'UserName'
EXEC sp_update_jobstep @job_name = 'syspolicy_purge_history', @step_id = 3, @database_user_name = 'UserName'
EXEC sp_update_jobstep @job_name = 'syspolicy_purge_history', @step_id = 2, @database_user_name = 'UserName'
EXEC sp_update_jobstep @job_name = 'syspolicy_purge_history', @step_id = 1, @database_user_name = 'UserName'

Copy all the statements by selecting on the column header (OutputStatements) and hit CTRL+C. Open a new query window and paste the results CTRL+V into the query window. Remove any EXEC statement/lines you see for maintenance jobs or others you do not want to update with a database user.

Now you should be left with the sp_update_jobstep commands for those you truly want to update. Execute the query by hitting !Execute or F5.

To see your new job step details, use this statement:

SELECT sj.name, sjs.job_id, sjs.step_id, sjs.database_user_name
FROM dbo.sysjobsteps sjs
INNER JOIN dbo.sysjobs sj
    ON sjs.job_id = sj.job_id

Happy job step changing. :)

LaraRaraBoBara
  • 628
  • 1
  • 6
  • 15
  • I copy and paste the select statement and replace username with my username, but when I execute, it gave me an error Invalid object name 'dbo.sysjobsteps'. What am I doing wrong? – user3083228 Jan 03 '14 at 19:01
  • nevermind...I figure out what I was doing wrong in the previous post...thanks for your help – user3083228 Jan 03 '14 at 19:06
  • You need to be in the MSDB database, edited the above answer to include that information. Please mark as answered if it works and helped you. Thanks! – LaraRaraBoBara Jan 03 '14 at 19:19
  • I got the query to run, but it still doesn't change the name of the Run As person, within the stepId of the job. I only try to run it with one stepid and 1 job but it doesn't change the Run As: user. – user3083228 Jan 03 '14 at 19:25
  • If you go to the step itself, under Advanced tab it should say the new user. Also, if you run the final SELECT statement, does it say your new database user name in those fields? – LaraRaraBoBara Jan 03 '14 at 19:27
  • I ran the final Select statement, it still said "Null" in the database_user_name under the job and the stepid that I ran previously. But why advance tab? I just need it in the General tab of the step itself to change the Run As user... – user3083228 Jan 03 '14 at 19:31
  • You also need to make sure you (the user you are trying to change it to) have rights to the jobs. If you are not a sysadmin, then under the msdb database, grant yourself all of these database role memberships: SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole. – LaraRaraBoBara Jan 03 '14 at 19:33
  • As far as the advanced tab, see the answer to this similar discussion here: [stackoverflow] (http://stackoverflow.com/questions/1216404/sql-server-agent-job-account-issue) – LaraRaraBoBara Jan 03 '14 at 19:39
  • I double check myself...by clicking on my username and checking my server role...it shows I'm both sysadmin and serveradmin – user3083228 Jan 03 '14 at 19:39
  • You may be attempting to change a step that has to use a system user, for example a PowerShell type. You should be able to see the Type under the Job Properties, Type. Can you verify the step you are trying to modify/test with is not a PowerShell type? If so, please test using another job step. – LaraRaraBoBara Jan 03 '14 at 19:42
  • The job type of the step I try to change is Operating System (CmdExec), not sure if this is the PowerShell type you're referring. – user3083228 Jan 03 '14 at 19:47
  • Yes, please test this with a non cmdexec or PowerShell step if you have one to test. – LaraRaraBoBara Jan 03 '14 at 19:48
  • But the ones that I want to change...all of them are Operating System (CmdExec)...but unfortunately there are no non cmdexec – user3083228 Jan 03 '14 at 19:53
  • Then you would need to create a proxy account. Please see technet article which explains the cmdexec steps are run as SQL Server Agent service account (http://technet.microsoft.com/en-us/library/ms190264(v=sql.90).aspx). And here is a stack exchange question/answer on how to set up a proxy (http://stackoverflow.com/questions/12112276/sql-agent-job-run-as-drop-down-list-is-empty). I personally would warn against doing this as once you change the run as on CmdExec steps you have to ensure you have correct permissions, which on 300+ jobs may be problematic. – LaraRaraBoBara Jan 03 '14 at 19:58
  • I've created the proxy account and the credential needed. But when I ran the above query again, it still doesn't work, I am able to manually modified if I go into the step itself, but not able to do it by query, please help. – user3083228 Jan 03 '14 at 22:16
  • Your original post/question indicated the user was someone other than you as the "run as", but then you mentioned these are all Cmd Exec steps and the users are NULL. I would go back to my advice to not change these. I do not see an automated way to change the run as for the non TSQL job steps. – LaraRaraBoBara Jan 06 '14 at 17:33