xp_cmdshell

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

  • 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

  • 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 .

  • 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 '\')
    );

     

  • 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%';
  • Thanks  for the help ...

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

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

  • 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

     

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

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