Post reply

script to find all jobs that invokes xp_cmdshell and the step scheduled time

  • September 19, 2022 at 8:04 pm

    #4089534

    I have a script that list all jobs that invokes xp_cmdshell:

    declare @pattern nvarchar(max)
    set @pattern='xp_cmdshell'
    select  name,enabled
    from msdb.dbo.sysjobs j
    inner join msdb.dbo.[sysjobsteps] s on j.job_id=s.job_id 
    where s.command like '%'+@pattern+'%'
    order by j.name

    however, I'm trying also list (as a separate column) with the scheduled time those job executes.

    thanks for any assistance

    • This topic was modified 2 years, 2 months ago by  lan2022nguyen.
    • This topic was modified 2 years, 2 months ago by  lan2022nguyen.
    • This topic was modified 2 years, 2 months ago by  lan2022nguyen.

  • September 19, 2022 at 10:39 pm

    #4089578

    i found a script.  this can be close

  • September 20, 2022 at 1:23 am

    #4089598

    lan2022nguyen wrote:

    i found a script.  this can be close

    You do realize that's not going to find any job that executes a stored procedure that executes xp_CmdShell, correct?

    This sounds a bit like a witch hunt... why do you need to do this?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 20, 2022 at 9:49 am

    #4089723

    hi jeff, i have a script to list all SPs that invokes xp_cmdshell.

    our db security team has identify xp_cmdshell  as a security finding.  they want us to disable this xp.

    as a workaround, i was planning to enable before the execution and disable when the proc/job is finish.  only caveat - what happens when both job/proc executes simultaneously?  who will turn on/off last?

    long term, we have to re-write instead of using this xp.

    thanks for any suggestions.

Viewing 4 posts - 1 through 3 (of 3 total)

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