Tuesday, September 9, 2014

CMS: List all Jobs and Their Owners on All Servers

Using CMS, we can get a list of all the Jobs on all servers that are not owned the way we would like. The code below is just an example of how you can exclude by server name, job name, and owner name. SQL Server 2005+ only. Note that this example excludes the 'sa' user, as usually it's considered an allowable owner.

-- Exclude SQL Server 2000 servers, and some specific servers.
set nocount on

if '8' <> convert(nvarchar(1), serverproperty('ProductVersion'))
and @@servername not in ('MYDEVSERVER', 'MYTESTSERVER')

     select j.name   as JobName
          , l.name   as JobOwner
       from msdb..sysjobs   j
  left join sys.syslogins   l
         on j.owner_sid = l.sid
      where j.enabled = 1
        and j.name <>       'syspolicy_purge_history'
        and j.name not like 'CDW\_%'
        and j.name not like 'dtexecRemote%'
        and l.name <>       'sa'
        and l.name <>       'MYDOMAIN\MY_PREFERRED_OWNER'   -- Change this.
   order by JobName


Post a Comment