how to find sql job reference

quote from
http://stackoverflow.com/questions/18105547/how-to-find-all-sql-agent-jobs-that-call-a-given-stored-proc



SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sJSTP].[step_uid] AS [StepID]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[subsystem]
        WHEN 'ActiveScripting' THEN 'ActiveX Script'
        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
        WHEN 'PowerShell' THEN 'PowerShell'
        WHEN 'Distribution' THEN 'Replication Distributor'
        WHEN 'Merge' THEN 'Replication Merge'
        WHEN 'QueueReader' THEN 'Replication Queue Reader'
        WHEN 'Snapshot' THEN 'Replication Snapshot'
        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
        ELSE sJSTP.subsystem
      END AS [StepType]
    , [sPROX].[name] AS [RunAs]
    , [sJSTP].[database_name] AS [Database]
    , [sJSTP].[command] AS [ExecutableCommand]
    , CASE [sJSTP].[on_success_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: '
                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
                    + ' '
                    + [sOSSTP].[step_name]
      END AS [OnSuccessAction]
    , [sJSTP].[retry_attempts] AS [RetryAttempts]
    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
    , CASE [sJSTP].[on_fail_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: '
                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
                    + ' '
                    + [sOFSTP].[step_name]
      END AS [OnFailureAction]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [sJSTP].[job_id] = [sOSSTP].[job_id]
        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [sJSTP].[job_id] = [sOFSTP].[job_id]
        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
WHERE [sJSTP].[command] LIKE '%yourjobname%'
ORDER BY [JobName], [StepNo]

CANNOT DROP SERVER ‘REPL_DISTRIBUTOR’ BECAUSE IT IS USED AS A DISTRIBUTOR

quote from

https://sqlaj.wordpress.com/2012/03/23/cannot-drop-server-repl_distributor-because-it-is-used-as-a-distributor/

We just went through our annual IG audits and several servers I manage were shown to have vulnerabilities. After further inspection into the issue it appears I was hit with “DBMS remote system credential use and access” and “Replication use and security”. Since replication was a requirement initially and has since changed I am able to clear all the replication functionality off the server.

When trying to drop the repl_distributor “linked” server by just right clicking on the the server and trying to delete the server the following error was produced.

Even though I figured this would fail as well I thought I would script out the linked server in a drop statement I received the following error.

A little more digging and querying the sys.servers table it appears the “is_distributor” column is set to 1 indicating it is still being used in replication and the source of the error. Keep in mind I have already deleted the publication and subscription (on a different server). These are just the remnants of the distribution and need to be cleaned up. So let’s try and update the table and set the value to 0.

Oops. That is not allowed here. Reading more on Remote Servers and Linked Servers I found this stored procedure sp_serveroption. It sets server options for remote servers and linked servers and can be used. You can use the following code.

Checking to make sure we updated the value correctly.

Now we can drop the linked server with the code we scripted out originally.

And confirm the linked server is gone.

Here is a link for more information on the sp_serveroption.

http://msdn.microsoft.com/en-us/library/ms178532.aspx

Just remember, when requirements change and you are cleaning up the server, to remove all the aspects of the functionality you are removing.

Cheers!