-1

Need help to disable xp_cmdshell from below code. what are the procedure we can to take disable xp_cmdshell.

-- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'ExportXYZPRTE', @owner_login_name = N'SORTADM', @description = N'Export TAPPRTE Table', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'export', @command = N'DECLARE @CMD varchar(100)
--Export from XYZPRTE Table
SET @CMD = ''dtsrun /FC:\program files (x86)\XYZAPPS\OAS\programs\XYZPRTEExport.dts''
EXEC master..xp_cmdshell @CMD', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Please help me how to replace this xp_cmdshell.

sid
  • 9
  • 2
  • 1
    What do you mean by ` what are the procedure we can to take disable xp_cmdshell.` ? – Squirrel Feb 15 '22 at 04:33
  • DTS was for the obsolete today SQL Server 2000. Starting from 2005 onwards it is SSIS. – Yitzhak Khabinsky Feb 15 '22 at 05:08
  • So you've read the documentation, [xp_cmdshell (Transact-SQL)](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql) and [xp_cmdshell Server configuration option](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option) and are still looking for something disable it? Or are you looking for a tooling alternative? – AlwaysLearning Feb 15 '22 at 05:27

1 Answers1

1

Here is a procedure that I wrote a while back - it just deletes some *.csv files on the server. Just replace the delete statement from here and replace with what ever you are doing.

CREATE PROCEDURE DeleteCSV
    -- Add the parameters for the stored procedure here
    @Result as VARCHAR(10) OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
BEGIN TRY
    -- Insert statements for procedure here
EXEC sp_configure 'show advanced options', '1'
RECONFIGURE


-- this enables xp_cmdshell
EXEC sp_configure 'xp_cmdshell', '1' 
RECONFIGURE
DECLARE @cmd NVARCHAR(MAX) = 
'xp_cmdshell ''del "C:\temp\*.csv"'''
EXEC (@cmd)
-- this disables xp_cmdshell
EXEC sp_configure 'xp_cmdshell', '0' 
RECONFIGURE
-- this turns off advanced options and is needed to configure xp_cmdshell
EXEC sp_configure 'show advanced options', '0'
RECONFIGURE

SET @Result = 'Success'
print @Result
END TRY
BEGIN CATCH
SET @Result = 'Failure'
END CATCH
END
duerzd696
  • 304
  • 1
  • 8