HowTo Generate List of SQL Server Jobs and their owners

Question

How would I go about generating a list of sql jobs and their owners  I would also like to be able to generate this list for SSIS packages also   Thanks

User · Accepted Answer

try this  Jobs  select s name l name  from  msdb  sysjobs s   left join master sys syslogins l on s owner sid   l sid   Packages  select s name l name  from msdb  sysssispackages s   left join master sys syslogins l on s ownersid   l sid

User · Answer

A colleague told me about this stored procedure      USE msdb  EXEC dbo sp help job

User · Answer

If you don t have access to sysjobs table  someone elses server etc  you might be have or be allowed access to sysjobs view  SELECT    from  msdb  sysjobs view s   left join master sys syslogins l on s owner sid   l sid   or   SELECT    SUSER SNAME s owner sid  AS owner  from  msdb  sysjobs view s

User · Answer

There is an easy way to get Jobs  Owners info from multiple instances by PowerShell   Run the script in your PowerShell ISE   Loads SQL Powerhell SMO and commands   Import-Module SQLPS -disablenamechecking   BUild list of Servers manually  this builds an array list     SQLServers    SERVERNAME INSTANCE01   SERVERNAME INSTANCE02   SERVERNAME INSTANCE03    SysAdmins    null  foreach  SQLSvr in  SQLServers           - Add Code block       MySQL   new-object Microsoft SqlServer Management Smo Server  SQLSvr      DIR SQLSERVER  SQL  SQLSvr JobServer Jobs  FT  SQLSvr  NAME  OWNERLOGINNAME -Auto         - End of Code block

User · Answer

It s better to use SUSER SNAME   since when there is no corresponding login on the server the join to syslogins will not match  SELECT  s name           SUSER SNAME s owner sid  AS owner FROM    msdb  sysjobs s  ORDER BY name

[sql-server] HowTo Generate List of SQL Server Jobs and their owners

Examples related to sql-server