Post reply

Get Job Information

  • September 6, 2007 at 10:01 am

    #180631

    I am trying to get job information and have a query set up. However, I would also like to get the owner information. However, the job only stores the sid of the owner. Does anybody know how I could change this query to get the owner name instead of sid?

    SELECT @@SERVERNAME AS Server, msdb.dbo.sysjobs.job_id, msdb.dbo.sysjobs.name, msdb.dbo.sysjobs.enabled AS Job_Enabled,

    msdb.dbo.sysjobs.description, msdb.dbo.sysjobs.notify_level_eventlog, msdb.dbo.sysjobs.notify_level_email,

    msdb.dbo.sysjobs.notify_level_netsend, msdb.dbo.sysjobs.notify_level_page, msdb.dbo.sysjobs.notify_email_operator_id,

    msdb.dbo.sysjobs.date_created, msdb.dbo.syscategories.name AS Category_Name, msdb.dbo.sysjobschedules.next_run_date,

    msdb.dbo.sysjobschedules.next_run_time, msdb.dbo.sysjobservers.last_run_outcome, msdb.dbo.sysjobservers.last_outcome_message,

    msdb.dbo.sysjobservers.last_run_date, msdb.dbo.sysjobservers.last_run_time, msdb.dbo.sysjobservers.last_run_duration,

    msdb.dbo.sysoperators.name AS Notify_Operator, msdb.dbo.sysoperators.email_address, msdb.dbo.sysjobs.date_modified, GETDATE()

    AS Package_run_date

    FROM msdb.dbo.sysjobs INNER JOIN

    msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id LEFT OUTER JOIN

    msdb.dbo.sysoperators ON msdb.dbo.sysjobs.notify_email_operator_id = msdb.dbo.sysoperators.id LEFT OUTER JOIN

    msdb.dbo.sysjobservers ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id LEFT OUTER JOIN

    msdb.dbo.sysjobschedules ON msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id

  • September 6, 2007 at 12:35 pm

    #731948

    Without looking too closely at your query, I would say you most likely need to go to sys.server_principals to look that up.  Or you could use the user_name(ID) function.

  • September 6, 2007 at 2:58 pm

    #731978

    select suser_sname(SID)


    * Noel

  • September 6, 2007 at 3:17 pm

    #731985

    oops Noeld is correct.  One I said returns the user name in the database, suser_sname returns the login name.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply