Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Error: Only a member of the sysadmin server role can add a job for a different owner with @owner_login_name

Infrastructure: SQL server 2005 and onward

Scenario: Here the scenario was, user with DB level access say database owner was trying to create sql job and wanted to change the owner to sa or some other sysadmin account.User also had role in MSDB role as of result she was able to see the jobs and create the jobs but unable to change its owner to sysadmin.

Please refer link for msdb agent role.

 

Resolution:

As per the error, only sysadmin can change the owner to sysadmin sql account or some other account. Once the jobs got created DBA can change the owner and issues disappear but generally application team like to manage their own application related group of jobs without depending on DBA everytime.

In such situation, either DBA needs to create proxy for them through which they can run the job under SQL security context or application team can also do hard-code inside their tsql code to EXECUTE AS.

but all the time EXECUTE AS the code dosent look flexibile and if its SSIS package that needs to run with help of SQL agent job in that case proxy is required.

Please note, t-sql can not be run through proxy account. So in this scenario, proxy can help them out.

Please follow this link to understand and implementing proxy account.

Hope this helps!