Question

A SQL Server Agent job on an on-premises SQL Server 2022 instance includes a CmdExec step that runs a PowerShell script to copy backup files to a network share. The job step must execute under a specific Windows domain account that has write access to the share. The job owner is a member of the db_owner role but is NOT a member of the sysadmin fixed server role. When the job runs, the CmdExec step fails with a permission error. What must you configure so the step runs successfully under the required domain account?

Accepted Answer

Create a credential mapped to the domain account, create a SQL Server Agent proxy for the CmdExec subsystem based on that credential, and set the job step to run as the proxy.

Answer

Add the job owner to the sysadmin fixed server role so the CmdExec step runs under the SQL Server Agent service account.

Answer

Grant the domain account the db_datawriter role in the target database and set the job step to run as the database user.

Answer

Enable xp_cmdshell at the server level and add the job owner to the SQLAgentOperatorRole.