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 (890775)

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: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.

Method 1: Use SQL Server Enterprise Manager

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.
3. In the SQL Server Login Properties dialog box, click the Database Access tab.
4. 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.
5. Expand Databases, expand master, and then click Extend Stored Procedures.
6. Locate and then right-click the xp_cmdshell extended stored procedure, and then click Properties.
7. In the Extended Stored Procedure Properties dialog box, click Permissions.
8. 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'<DomainName>'
                        , N'<WindowsAccount>'
                        , N'<WindowsPassword>'
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, <DomainName> is the domain that the Windows account is a member of. <WindowsAccount> is the Windows account name. <WindowsPassword> 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 '<SQLLogin>'
GO

-- Grant execute permission on xp_cmdshell to the SQL Server login account.
GRANT exec ON xp_cmdshell TO <SQLLogin>
GO

Note In this script, <SQLLogin> 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 "Log on as a batch job" user right.