Microsoft KB Archive/890775

= How to configure a SQL Server Agent proxy account to enable non-system administrators to execute the xp_cmdshell extended stored procedure in SQL Server 2000 =

Article ID: 890775

Article Last Modified on 9/12/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-





SUMMARY
''This article describes how to configure a Microsoft Windows-level SQL Server Agent proxy account to be used by SQL logins that are not members of the sysadmin fixed server role. Additionally, the article contains references to more information to help you solve problems that may occur when you try to set up a proxy account.''



INTRODUCTION
The xp_cmdshell extended stored procedure invokes a Windows command shell from within Microsoft SQL Server 2000. When the xp_cmdshell extended stored procedure is executed by a member of the sysadmin fixed server role, the Windows process of the command shell runs with the security context of the Windows service account of SQL Server. For example, if SQL Server is running under a Windows logon that has administrative rights on the computer, the user who opens a command prompt with the xp_cmdshell extended stored procedure will receive access to Windows as a local administrator. By default, the permission to execute the xp_cmdshell extended stored procedure is granted only to members of the sysadmin fixed server role. This permission helps deter the escalation of rights.

When you want to start new Windows processes with a reduced level of rights, you can use the SQL Server Agent proxy account to configure a Windows account with the appropriate level of rights.



MORE INFORMATION
When you try to execute the xp_cmdshell extended stored procedure from a SQL Server login that is not a member of the sysadmin server role and the SQL Server Agent proxy account has not been set up correctly, you may receive error messages that are similar to the following:

Msg 50001, Level 1, State 50001

xpsql.cpp: Error 997 from GetProxyAccount on line 499

Msg 50001, Level 1, State 50001

xpsql.cpp: Error 317 from GetProxyAccount on line 499

Msg 50001, Level 1, State 50001

xpsql.cpp: Error 0 from GetProxyAccount on line 499

Msg 50001, Level 1, State 50001

xpsql.cpp: Error 87 from GetProxyAccount on line 499

To configure a proxy account for SQL Server logins that are not members of the sysadmin fixed server role, use one of the following methods.

Configure the Windows account
To set the Windows account to enable non-sysadmin users to execute the xp_cmdshell extended stored procedure, follow these steps:
 * 1) Start SQL Server Enterprise Manager.
 * 2) In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then expand SQL Server Group.
 * 3) Expand the instance of SQL Server that you want to set up the proxy account for.
 * 4) Expand Management, right-click SQL Server Agent, and then click Properties.
 * 5) In the SQL Server Agent Properties dialog box, click the Job System tab.
 * 6) Under Non-SysAdmin job step proxy account, click to clear the Only users with SysAdmin priviledges can execute CmdExec and ActiveScripting job steps check box.
 * 7) In the SQL Server Agent proxy account dialog box, type the Windows account name in the User name box.
 * 8) In the Password box, type the Windows password.
 * 9) In the Domain box, type the domain that the Windows account is a member of, and then click OK two times.

Add a SQL Server login account
To add a SQL Server login account that you want to grant permissions to execute the xp_cmdshell extended stored procedure, follow these steps:
 * 1) In SQL Server Enterpise Manager, expand Security.
 * 2) In the Logins pane, right-click the SQL Server login account that you want to grant permissions to, and then click Properties.

Note If the account is not already added, you can create a Windows proxy account as the login account.
 * 1) In the SQL Server Login Properties dialog box, click the Database Access tab.
 * 2) In the Specify which database can be accessed by this login list, click to select the Permit check box for the master database, and then click OK.
 * 3) Expand Databases, expand master, and then click Extend Stored Procedures.
 * 4) Locate and then right-click the xp_cmdshell extended stored procedure, and then click Properties.
 * 5) In the Extended Stored Procedure Properties dialog box, click Permissions.
 * 6) Click to select the EXEC check box for the SQL Server login account that you want to grant execute permissions to, and then click OK two times.

Method 2: Run a Transact-SQL script
Run the following Transact-SQL script to enable the Windows account to execute the xp_cmdshell extended stored procedure: -- Set database to master. USE master GO

-- Add a valid Windows account as proxy account. EXECUTE xp_sqlagent_proxy_account N'SET' , N'' , N'' , N'' GO -- Get the proxy account to determine whether it is set up correctly. EXECUTE xp_sqlagent_proxy_account N'GET' GO

-- Enable non-system administrators to run the job and to execute xp_cmdshell. EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0 GO Note In this script,  is the domain that the Windows account is a member of. is the Windows account name. is the password for the Windows account.

To add the SQL Server login account that you want to grant permission to execute the xp_cmdshell extended stored procedure, run the following Transact-SQL script: USE master GO

-- Grant database access to the SQL Server login account that you want to provide access. EXEC sp_grantdbaccess '' GO

-- Grant execute permission on xp_cmdshell to the SQL Server login account. GRANT exec ON xp_cmdshell TO  GO Note In this script,  is the SQL Server login that you want to grant permission to execute the xp_cmdshell extended stored procedure.

Note The proxy account is the Windows account in whose security context the SQL Server Agent jobs or command-line commands are run. When you set up the Windows account for the SQL Server Agent proxy account, you must give the account the Windows-level &quot;Log on as a batch job&quot; user right.

