Microsoft KB Archive/833559

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

Article ID: 833559

Article Last Modified on 4/29/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



Bug #: 470553 (SQL Server 8.0)



SYMPTOMS
If you use a domain user account that has the &quot;Act as part of operating system&quot; and the &quot;Replace a process level token&quot; permissions that are described in the &quot;Setting up Windows Services Accounts&quot; topic to run the Microsoft SQL Server service, you may receive the following error message when you try to run the xp_cmdshell extended stored procedure:

Msg 50001, Level 1, State 50001

xpsql.cpp: Error 997 from GetProxyAccount on line 604



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 &quot;Symptoms&quot; 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.

