In SQL Server 2000, a domain user cannot run the xp_cmdshell extended stored procedure even when the user has permissions (833559)

MORE INFORMATION

Do not allow the users who are not members of the sysadmin fixed server role to run the xp_cmdshell extended stored procedure. However, if you have to allow the non-sysadmin SQL Server users to run the xp_cmdshell extended stored procedure, the Windows service accounts that are configured for SQL Server must be included as members of the Administrators group on the computer that is running SQL Server.

In SQL Server 2000, if you have to allow the non-sysadmin SQL Server users to run the xp_cmdshell extended stored procedure, you must configure the proxy account. When SQL Server executes jobs or commands for users who are not members of the sysadmin fixed server role, the SQL Server Agent and the xp_cmdshell extended stored procedure use the proxy account. The Windows security credentials for the proxy account are stored in the Local Security Authority (LSA) Secrets database, and only the Windows Administrators can access the information. Therefore, if the domain user account is not a member of the local administrator group, the user cannot store or retrieve the Windows security credentials to log on as the proxy account. Therefore, the xp_cmdshell extended stored procedure fails, and the user receives the error message that is described in the "Symptoms" section of this article.

A domain user who is not a member of the sysadmin fixed server role can run the xp_cmdshell extended stored procedure. The user can run the xp_cmdshell extended stored procedure even when the SQL Server startup account is not a local administrator account. The SQL server agent service account and not the SQL Server service account needs to have local administrator rights.

The following two items determine whether a CmdExec/ActiveScript job step can be executed:

• The user who starts the job
• The owner of the job

When the Only Users with sysadmin privileges can Execute CmdExec and ActiveScripting job steps check box is selected on the Job System tab of the SQL Server Agent properties dialog box, the following conditions are true:

• SysAdmin users can start their own jobs with CmdExec/ActiveScript steps. The CmdExec/ActiveScripting steps are executed with the SQLAgent service account.
• SysAdmin users cannot start jobs with CmdExec/ActiveScript steps that are owned by other users. The steps fail with a permission denied error for non-sysadmin users.
• Non-sysAdmin users cannot start jobs with CmdExec/ActiveScript steps. The steps fail with a permission denied error for non-sysadmin users. These users can only see their own user jobs listed.

When the Only Users with sysadmin privileges can Execute CmdExec and ActiveScripting job steps check box is NOT selected on the Job System tab of the SQL Server Agent properties dialog box, the following conditions are true:

• Sysadmins can start their own jobs with CmdExec/ActiveScript steps. The steps are executed with the SQLAgent service account, as before.
• Sysadmin users can start jobs with CmdExec/ActiveScript steps owned by other users. The steps are executed by using the SQLAgentProxy account.
• Non-sysadmin users can start jobs with CmdExec/AcvtiveScript steps. The steps are executed by using the SQLAgentProxy account.
  
  Note For the last two entries in the list, the SQLAgentProxy account is not required to be a local administrator. However, the SQL Server Agent service account is required to be a local administrator. All access permissions for the SQLAgentProxy account are subject to regular/normal access permissions.