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.
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.
xpsql.cpp: Error 997 from GetProxyAccount on line 499
xpsql.cpp: Error 317 from GetProxyAccount on line 499
xpsql.cpp: Error 0 from GetProxyAccount on line 499
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.
Method 1: Use SQL Server Enterprise Manager
Configure the Windows accountTo set the Windows account to enable non-sysadmin users to execute the xp_cmdshell extended stored procedure, follow these steps:
- Start SQL Server Enterprise Manager.
- In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then expand SQL Server Group.
- Expand the instance of SQL Server that you want to set up the proxy account for.
- Expand Management, right-click SQL Server Agent, and then click Properties.
- In the SQL Server Agent Properties dialog box, click the Job System tab.
- 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.
- In the SQL Server Agent proxy accountdialog box, type the Windows account name in the User namebox.
- In the Password box, type the Windows password.
- 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 accountTo add a SQL Server login account that you want to grant permissions to execute the xp_cmdshell extended stored procedure, follow these steps:
- In SQL Server Enterpise Manager, expand
- In the Logins pane, right-click the SQL Server login account that you want to grant permissions to, and then click
Note If the account is not already added, you can create a Windows proxy account as the login account.
- In the SQL Server Login Properties dialog box, click the Database Access tab.
- 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.
- Expand Databases, expand
master, and then click Extend Stored Procedures.
- Locate and then right-click the
xp_cmdshell extended stored procedure, and then click
- In the Extended Stored Procedure Properties dialog box, click Permissions.
- 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 scriptRun the following Transact-SQL script to enable the Windows account to execute the xp_cmdshell extended stored procedure:
-- Set database to master.
-- Add a valid Windows account as proxy account.
EXECUTE xp_sqlagent_proxy_account N'SET'
-- Get the proxy account to determine whether it is set up correctly.
EXECUTE xp_sqlagent_proxy_account N'GET'
-- Enable non-system administrators to run the job and to execute xp_cmdshell.
EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0
<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:
-- Grant database access to the SQL Server login account that you want to provide access.
EXEC sp_grantdbaccess '<SQLLogin>'
-- Grant execute permission on xp_cmdshell to the SQL Server login account.
GRANT exec ON xp_cmdshell TO <SQLLogin>
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.
For more information about the SQL Server Agent proxy account, visit the following Microsoft Developer Network (MSDN) Web sites:
Setting up Windows Services Accounts
Article ID: 890775 - Last Review: 29 Mar 2017 - Revision: 2