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

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

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
}

A colleague told me about this stored procedure...

USE msdb

EXEC dbo.sp_help_job

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 

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