Least privilege for job steps: SQL Server Agent proxies and credentials explained

Without a proxy, a CmdExec or PowerShell step owned by a sysadmin runs as the SQL Server Agent service account. That account then needs rights to every file share, folder and remote server any job touches, so it ends up far too powerful. A proxy lets one step run as a specific Windows account with only the rights that step needs.

#How the pieces fit

Windows account (CORP\svc-finance-files)
      ↑  stored securely as
Credential (server level: identity + secret)
      ↑  wrapped by
Proxy (Agent object) ──granted to──► subsystems (CmdExec, PowerShell, SSIS...)
      │                 ──granted to──► logins / roles that may use it
      ↓
Job step "Run as: Finance file proxy"

T-SQL steps don't use proxies. They run as the job owner, or in the Agent service context if the owner is sysadmin. Use EXECUTE AS inside the code if a T-SQL step needs a different database identity.

#Step 1: create the credential

CREATE CREDENTIAL [cred_finance_files]
WITH IDENTITY = N'CORP\svc-finance-files', SECRET = N'the-account-password';

Better options than a stored password:

• A group managed service account (gMSA). Use the account name with a $ and an empty secret where your version supports it, and test it, because support varies by subsystem and version.
• Store and rotate the password in your password vault, then ALTER CREDENTIAL on rotation.

The Windows account needs "Log on as a batch job" on the SQL Server host (grant it through group policy).

#Step 2: create the proxy and grant subsystems

EXEC msdb.dbo.sp_add_proxy @proxy_name = N'Finance file proxy', @credential_name = N'cred_finance_files',
     @description = N'Reads/writes \\files01\finance only', @enabled = 1;

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = N'Finance file proxy', @subsystem_name = N'CmdExec';
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = N'Finance file proxy', @subsystem_name = N'PowerShell';
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = N'Finance file proxy', @subsystem_name = N'SSIS';

-- list subsystems on this server
EXEC msdb.dbo.sp_enum_sqlagent_subsystems;

#Step 3: allow logins to use the proxy (non-sysadmin owners)

Sysadmins can use any proxy. A non-sysadmin job owner can only pick proxies granted to them, either directly, through a server role, or through an msdb role:

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name = N'Finance file proxy', @login_name = N'CORP\svc-finance-jobs';
-- or to an msdb role
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name = N'Finance file proxy', @msdb_role = N'FinanceJobOwners';

#Step 4: use it in a job step

EXEC msdb.dbo.sp_add_jobstep @job_name = N'Finance - Export - GL files', @step_name = N'Copy to share',
     @subsystem = N'PowerShell', @proxy_name = N'Finance file proxy',
     @command = N'Copy-Item -Path "D:\Exports\GL\*.csv" -Destination "\\files01\finance\gl\" -Force -ErrorAction Stop';

In SSMS: Job step → Run as → choose the proxy. The list only shows proxies granted to that step's subsystem and to the job owner.

#Audit proxies

SELECT p.name AS proxy, p.enabled, c.name AS credential, c.credential_identity,
       STRING_AGG(sub.subsystem, ', ') AS subsystems          -- STRING_AGG needs SQL Server 2017+
FROM msdb.dbo.sysproxies p
JOIN sys.credentials c ON c.credential_id = p.credential_id
LEFT JOIN msdb.dbo.sysproxysubsystem ps ON ps.proxy_id = p.proxy_id
LEFT JOIN msdb.dbo.syssubsystems sub ON sub.subsystem_id = ps.subsystem_id
GROUP BY p.name, p.enabled, c.name, c.credential_identity;

-- which steps use which proxy
SELECT j.name AS job_name, s.step_name, s.subsystem, p.name AS proxy
FROM msdb.dbo.sysjobsteps s JOIN msdb.dbo.sysjobs j ON j.job_id = s.job_id
LEFT JOIN msdb.dbo.sysproxies p ON p.proxy_id = s.proxy_id
WHERE s.subsystem <> N'TSQL' ORDER BY proxy, job_name;

-- logins allowed to use each proxy
EXEC msdb.dbo.sp_enum_login_for_proxy;

Any non-T-SQL step with proxy = NULL runs as the Agent service account. Review each one.

#Troubleshooting

Tip

One proxy per purpose (Finance file export, HR SFTP), not one per job and not one for everything. That keeps each account's rights small and the audit easy to read.