Tag Archives: disable

How to disable all enabled user job schedules in SQL Server with T-Sql

I’d really recommend not running the output of this script on a live environment! This is just a little something I whipped up to test on a development environment.

Running the below script will output the T-Sql required to disable all enabled job schedules on the SQL Server instance. Just copy the outputted text and paste into a new SSMS window and execute to disable the jobs.

SET NOCOUNT ON

DECLARE @Schedule TABLE (ScheduleId INT)
DECLARE @ScheduleId AS INT
DECLARE @Sql AS VARCHAR(MAX)

SET @ScheduleId = ''

INSERT INTO @Schedule (ScheduleId)
SELECT s.schedule_id
FROM msdb.dbo.sysschedules AS s
WHERE s.[enabled] = 1
	AND s.owner_sid <> 0x01
ORDER BY s.NAME ASC

WHILE @ScheduleId IS NOT NULL
BEGIN
	SET @ScheduleId = (
			SELECT MIN(ScheduleId)
			FROM @Schedule
			WHERE ScheduleId > @ScheduleId
			)
	SET @Sql = '
EXEC msdb.dbo.sp_update_schedule @schedule_id = ' + '''' + CONVERT(VARCHAR(5), @ScheduleId) + '''' + '
	,@enabled = 0; 
'

	PRINT @Sql
END
Advertisements

How to disable all enabled jobs in SQL Server with T-Sql where the job name contains specific keyword text

Running the below script will output the T-Sql required to disable all enabled jobs on the SQL Server instance where the job name contains a specific keyword. Just copy the outputted text and paste into a new SSMS window and execute to disable the jobs.

For example, if you change the text value ‘PLACEHOLDER’, for the variable @KeyWord, to ‘Backup’ then any job which has ‘Backup’ in its name will have T-Sql generated to disable it. You could change the PRINT to EXEC but I wouldn’t recommend it.

DECLARE @KeyWord AS VARCHAR(256)

SET @KeyWord = 'PLACEHOLDER'
SET NOCOUNT ON

DECLARE @Job TABLE (JobName SYSNAME)
DECLARE @JobName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)

SET @JobName = ''

INSERT INTO @Job (JobName)
SELECT j.NAME
FROM msdb.dbo.sysjobs AS j
WHERE j.[enabled] = 1
	AND j.NAME LIKE '%' + @KeyWord + '%' 
ORDER BY j.NAME ASC

WHILE @JobName IS NOT NULL
BEGIN
	SET @JobName = (
			SELECT MIN(JobName)
			FROM @Job
			WHERE JobName > @JobName
			)
	SET @Sql = '
EXEC msdb.dbo.sp_update_job @job_name = ' + '''' + @JobName + '''' + '
	,@enabled = 0; 
'

	PRINT @Sql
END

How to enable and disable xp_cmdshell

xp_cmdshell is an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code.

By default, the xp_cmdshell option is disabled on new installations. Along with other unwanted behavior malicious users can elevate their privileges with the use of xp_cmdshell. For this reason it is generally recommend to leave xp_cmdshell disabled. It can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure as shown in the following code example:

Use Master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

 

To disable xp_cmdshell use the following code example. Note: The following example also sets show advanced options server configuration option to 0. It is best practice to leave this option in the default state of 0 to stop the enabling of features.

Use Master
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO