Script Every Operator

SQL Server Administration
1

hi experts,
How can I script out every agent job operator? i(I want to find all that reference my email address and manually change each to an email group)

Thanks very much.

2

You can access the email address by query msdb's sysoperators

1 Like
3

hi

hope this helps

-- Create a cursor to iterate through all jobs  
DECLARE @JobID UNIQUEIDENTIFIER  
DECLARE @JobName sysname  
DECLARE @StepID INT  

-- Table to store operator information  
CREATE TABLE #JobOperatorInfo (  
    JobName sysname,  
    JobID UNIQUEIDENTIFIER,  
    StepID INT,  
    StepName sysname,  
    SubSystem nvarchar(128),  
    Operator nvarchar(128)  
);  

-- Cursor to iterate through all jobs  
DECLARE cur_Jobs CURSOR FOR  
    SELECT name, job_id   
    FROM msdb.dbo.sysjobs   
    WHERE enabled = 1;  -- Only enabled jobs  

OPEN cur_Jobs;  

FETCH NEXT FROM cur_Jobs INTO @JobName, @JobID;  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- Get steps for the current job  
    DECLARE cur_JobSteps CURSOR FOR  
        SELECT step_id   
        FROM msdb.dbo.sysjobsteps   
        WHERE job_id = @JobID;  

    OPEN cur_JobSteps;  

    FETCH NEXT FROM cur_JobSteps INTO @StepID;  

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        -- Get operator information for each step  
        INSERT INTO #JobOperatorInfo (  
            JobName,   
            JobID,   
            StepID,   
            StepName,   
            SubSystem,   
            Operator  
        )  
        EXEC msdb.dbo.sp_help_jobstep   
            @job_id = @JobID,   
            @step_id = @StepID;  

        FETCH NEXT FROM cur_JobSteps INTO @StepID;  
    END  

    CLOSE cur_JobSteps;  
    DEALLOCATE cur_JobSteps;  

    FETCH NEXT FROM cur_Jobs INTO @JobName, @JobID;  
END  

CLOSE cur_Jobs;  
DEALLOCATE cur_Jobs;  

-- Now look for operators referencing the specific email address  
SELECT   
    joi.JobName,   
    joi.StepID,   
    joi.Operator  
FROM   
    #JobOperatorInfo joi  
WHERE   
    joi.Operator LIKE '%your.email@domain.com%';  

-- Optional: Export the results to a file  
-- sp_OACreate and other OLE Automation procedures may need to be enabled  
-- EXEC sp_configure 'show advanced options', 1; RECONFIGURE;   
-- EXEC sp_configure 'OLE Automation Procedures', 1; RECONFIGURE;  

-- If you want to export the results to a CSV file:  
/*  
DECLARE @SQL VARCHAR(MAX);  
SET @SQL = 'SELECT JobName, JobID, StepID, Operator FROM #JobOperatorInfo';  
EXEC master.dbo.xp_cmdshell 'bcp "' + @SQL + '" queryout "C:\Operators.csv" -T -c -S your_server_name';  
*/