I read somewhere that for non-TSQL SQL Server Agent jobs, the job runs under SQL Server Service Agent account when the job owner is the sysadmin.
I just don't get why we need to use proxies even the owner of the step is a member of sysadmin fixed server role.
Best Answer
Proxies allow non-sysadmins to manage and maintain SQL jobs
I had similar thoughts for the longest time. BUT we did finally find a valid business use for them. The short answer is, proxies allow you to delegate SQL Agent jobs to non-sysadmins so they can manage, change and/or execute those jobs using various hooks into powerful SQL features like SSIS, SSAS, command-line, etc.
For example, we are increasingly getting developers who are implementing data marts and ETL SQL jobs using SSIS and/or Analysis Services. But, as you might imagine, their coding prowess isn't always up to par. Our DBAs used to spend hours trouble-shooting the poor coding and ETL because they had the sysadmin rights to the SQL Agent jobs and the developers did not. And those SQL Agent ETL jobs can take forever to run. And if it breaks, you're running it again and again at the behest of the developers who can't do that.
Proxies change all that. With proxies enabled in SQL we can give the developers a credential that maps a the proxy to allow them to manage, monitor, and maintain those ETL/SSIS jobs. No more DBA interaction required.
Because their jobs are assigned with their proxy/credential account, they (the developers or non-sysadmins) can review those jobs, re-run them, change them etc.
For example, we associated their domain\user with a SQL Server Credential/Proxy as shown in Figure A and B. Figure B is the actual AD password of the user so you may need to Skype and share your screen so they can type in their password:
Figure A: Create a credential
Figure B: Associate the credential with an Active Directory non-sysadmin
Then, we created/assigned the credential to the proxy to the SQL job functions we wanted to delegate as shown in Figure C. We named our credential identically to the proxy--so don't be confused there.
Figure C: Associate the proxy with the credential for the job types you'd like to delegate.
Finally, we associated the proxy with the particular job. as shown in Figure D. Now, those developers have full control over those specific jobs and SQL Agent features within those jobs.
Figure D: Add/run the proxy to the jobs you'd like to delegate.
Note: If your organization has a mandatory AD password change every few months, you will have to share your screen and allow the user to change their password for their associated SQL credential.