SQL Server Agent Jobs Account

Question

Post reply

SQL Server Agent Jobs Account

ivanworm

Mr or Mrs. 500

Points: 590
More actions
May 20, 2010 at 10:42 pm

#144927

Good day,
i've come to a stage in which i need to change the security credentials / connection account (not sure the right term for it, i mean the account the job used to execute as scheduled). The server is within a domain environment, and the jobs had been created and saved with the domain user.
what i'm trying to do now is to change the saved user from the domain user to a local server user, but i can't seem to change it from the GUI. any help please?
thanks a lot and have a wonderful day

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

magasvs SSCertifiable Points: 7660 More actions · Answer 1

Try MSDB stored procedure:

sp_update_job @job_name = 'job_name', @owner_login_name ='login'

Make sure new login has permissions on SQL Server.

ivanworm Mr or Mrs. 500 Points: 590 More actions · Answer 2

Hi magasvs

thanks very much for the pointer. however 1 thing i wish to understand, does the security credential relate to the job ownership? or should i be saying, the job will be executed by the defined owner?

if the above is true, should there be a change of the security account password, it shall be propagated automatically, or i still need to update the password somewhere?

also, does this apply to all kinds of SQL Server Agent Jobs, including the integration services DTS packages?

magasvs SSCertifiable Points: 7660 More actions · Answer 3

Hi,

By default job owner is login that created job and this is in most cases member of sysadmin role. Later the owner could be changed to the login that is not sysadmin, but it has to be member of one of these MSDB database roles: SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole (see details here http://msdn.microsoft.com/en-us/library/ms188283.aspx?PHPSESSID=88447baea8de0de536f12f07ea46b2d1).

The job will be executed as the job owner, so you have to be sure that the job owner has permissions to run job steps (if connects to a user database and executes stored procedure - make sure new job owner has permissions to execute stored procedure).

As per the password - you will not need to update it, the new password will be propagated automatically.

ivanworm Mr or Mrs. 500 Points: 590 More actions · Answer 4

hi magavs

thanks again for your help..

i've tried to change the job owner, once from the GUI and another time using sp_job_update, but when i check back the job history (executed after owner changed) it was still executed by the originally saved owner (original owner is 'user', i tried changing it to 'user1' and 'user2'). am quite confused now..

thank you.

magasvs SSCertifiable Points: 7660 More actions · Answer 5

So, what are you trying to achieve? To change the job owner or execute the job as specific user?

Did the job run on schedule or you executed it manually?

magasvs SSCertifiable Points: 7660 More actions · Answer 6

Hi,

Have you read this article? http://www.sql-server-performance.com/articles/dba/sql_server_job_proxy_account_p1.aspx

I think it explains perfectly how to run jobs as different login.

ivanworm Mr or Mrs. 500 Points: 590 More actions · Answer 7

Hi magasvs,

i want to change the account used for the job to an alternate account. the job is scheduled to run automatically every day. initially the job was using this account called 'user', and that now i want to change it to use account 'user1' as 'user' no longer exist.

magasvs SSCertifiable Points: 7660 More actions · Answer 8

Hi,

Here is how I tested it:

1) logged in as sysadmin to SQL Server and created test user "user1", added user1 to the "SQLAgentUserRole","SQLAgentReaderRole" in msdb database

2) created test job with schedule to run every 5 minutes

3) after first run changed job owner: EXEC msdb.dbo.sp_update_job @job_id=N'test_job_id',@owner_login_name=N'user1'

4) After next job run checked job's history (at the job step level):

- first run (before owner change) - step executed as SQL Server Service account

- second run - "Executed as user: user1. The step succeeded."