enable non-sysadmin accounts to execute the "xp_cmdshell

Document from :https://support.microsoft.com/en-us/help/890775/how-to-enable-non-sysadmin-accounts-to-execute-the-xp-cmdshell-extende

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 sysadminfixed 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_cmdshellextended 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

If you have to let non-sysadmin users run the xp_cmdshell extended stored procedure in SQL Server 2000, you must configure the proxy account and the SQL Server startup account to be a member of the Local Administrators group. 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. Only Windows administrators can access this information. Therefore, if the SQL Server startup account is not a member of the Local Administrators group, the user cannot store or retrieve the Windows security credentials to log on as the proxy account, and the xp_cmdshell extended stored procedure fails. In order to enable non-sysadmin users to run the xp_cmdshell extended stored procedure, the SQL Server startup account must be a member of the Local Administrators group.

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 accountdialog box, type the Windows account name in the User namebox.
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 OKtwo 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.
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_cmdshellextended 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.

References

---

For more information about the  xp_cmdshell extended stored procedure, see the "Transact-SQL Reference" topic in SQL Server Books Online. 

For more information about the SQL Server Agent proxy account, visit the following Microsoft Developer Network (MSDN) Web sites:

xp_sqlagent_proxy_account
http://msdn2.microsoft.com/en-us/library/aa260700(SQL.80).aspx

xp_cmdshell
http://msdn2.microsoft.com/en-us/library/aa369561.aspx

Setting up Windows Services Accounts
http://msdn2.microsoft.com/en-us/library/aa176564(SQL.80).aspx

For more information about how to set appropriate permissions on the proxy account, click the following article number to view the article in the Microsoft Knowledge Base:

283811 How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005

转载于:https://www.cnblogs.com/roseHLF/p/9039682.html