0

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.

  • 1
    sysadmin is a SQL Server role. Windows has no idea what that is, since it’s isolated inside of SQL Server, so if you want the service account to do stuff in Windows, you need to either use a Windows account as the service account, or use a proxy (think of it as impersonation). – Aaron Bertrand Oct 25 '18 at 3:26
  • A good article to read about Setting Up Your SQL Server Agent Correctly, – SqlWorldWide Oct 25 '18 at 14:24
1

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

enter image description here

Figure B: Associate the credential with an Active Directory non-sysadmin

enter image description here

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.

enter image description here

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.

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.