SQL Server Administration: Find SQL Agent jobs not owned by the SA account. Jobs that are owned by domain users can stop working if the users account is dropped or disabled. Jobs with this problem can be fixed by simply changing the job owner to SA. Use the query below to find jobs that are not owned by SA. The last column is the statement needed to change the job owner via T-SQL or it can be changed using SSMS.
USE msdb
GO
--Find jobs not owned by SA
SELECT [name] AS JobName,
[description] AS JobDescription,
SUSER_SNAME(owner_sid) AS JobOwner,
'EXEC msdb.dbo.sp_update_job @job_name = ''' + name + ''', @owner_login_name = ''sa'';' AS ChangeJobOwnerCommand
FROM msdb.dbo.sysjobs
WHERE SUSER_SNAME(owner_sid) <>('sa')
GO
______________________________________________________________________________
SQLRx has worked hard to minimize the impact of monitoring SQL Servers to the point that we are able to monitor high transaction systems with little impact (1% load) on the target system. Contact us today to help you monitor your SQL servers! |
