how-to enable xp_cmdshell_proxy_account on windows server

USE master 

GO

--Step-1: Execute the following query from sql management Studio login as sa.

-- Create a user that will be used for running xp_cmdshell called cmdshelluser

CREATE LOGIN cmdshelluser WITH PASSWORD='pass'

-- Add a Windows domain account Domain\User as the SQL Agent Proxy account

-- where secrtePass is the actual set up password for this windows OS user

EXEC sp_xp_cmdshell_proxy_account 'hostName\username', 'secretPas';

-- Grant database access to the SQL Server login account that you want to provide access.

EXEC sp_grantdbaccess 'cmdshelluser'

-- Grant execute permission on xp_cmdshell to the SQL Server login account. 

GRANT exec ON sys.xp_cmdshell TO cmdshelluser 

GO

--Step-2: Verify that the account is created.

-- To confirm that the ##xp_cmdshell_proxy_account## credential has been created.

select * from sys.credentials

--Step-3: Test executing the xp_cmdshell sp.

EXECUTE AS login = 'cmdshelluser'

-- Execution of xp_cmdshell is allowed.

--And executes successfully!!!

EXEC xp_cmdshell 'DIR C:\*.*'