xp_cmdshell

Question

xp_cmdshell

ramyours2003

SSChampion

Points: 12266
More actions
March 2, 2021 at 10:27 am

#3852176

how to check which job or stored procedure using xp_cmdshell in sql server ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

andreas.kreuzberg SSCertifiable Points: 6183 More actions · Answer 1

Hi,

just disable xp_cmdshell and you will see, wich Job oder SP will end with an error. Otherwise you have to study the code of the Job and Stored Procedures.

Or you may can trace this events with extend events.

Kind regards

Andreas

ramyours2003 SSChampion Points: 12266 More actions · Answer 2

i have 50+ jobs in my server its not possible to go each step in job and see whether it is enabled or not , is any alternative way to check .

ratbak Ten Centuries Points: 1332 More actions · Answer 3

You can query sysjobs for sysjobsteps containing xp_cmdshell or using the CmdExec type:

SELECT * FROM msdb.dbo.sysjobs
WHERE EXISTS (SELECT * FROM msdb.dbo.sysjobsteps 
              WHERE sysjobsteps.job_id = sysjobs.job_id
              AND (subsystem = 'CmdExec' OR sysjobsteps.command LIKE N'%xp\_cmdshell%' ESCAPE '\')
              );

cphite SSCarpal Tunnel Points: 4272 More actions · Answer 4

To see if it's in a stored procedure somewhere:

SELECT DISTINCT
o.name AS Object_Name,
o.type_desc
FROM 
sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE 
m.definition Like '%cmdshell%';

ramyours2003 SSChampion Points: 12266 More actions · Answer 5

Thanks for the help ...

Do i need to execute the stored procedure query on individual databases or in master db

ramyours2003 SSChampion Points: 12266 More actions · Answer 6

i need to get the stored procedure details which are using xp_cmdshell on all databases

cphite SSCarpal Tunnel Points: 4272 More actions · Answer 7

You can do something like this... bear in mind it may take several minutes depending on how many databases you have, and how many procedures, etc.

declare @find nvarchar(50) = 'cmdshell', -- the string you're searching for
@command nvarchar(4000)
if object_id('tempdb.dbo.#results') is not null drop table #results 
create table #results ( dBaseName nvarchar(100),
[Object_name] nvarchar(100),
[type_desc] nvarchar(50),
[definition] nvarchar(max)
) 
declare @db nvarchar(100) 
declare myCur cursor for select [name] from sys.databases order by 1
open myCur 
fetch next from myCur into @db 
while @@fetch_status = 0
begin
set @command = 
'insert #results 
SELECT DISTINCT 
''' + @db + ''' as dBaseName,
o.name AS [Object_Name],
o.type_desc,
m.definition
FROM 
' + @db + '.sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE 
m.definition Like ''%' + @find + '%''
and
o.type_desc = ''SQL_STORED_PROCEDURE'''
exec (@command)
fetch next from myCur into @db 
end
close myCur
deallocate myCur
select * from #results order by 1, 2