Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Get owners of jobs in T-SQl

    Hi

    This should be straight forward but SQL Server is not responding as expected.

    I simply want to programmatically ascertain the owner of jobs. Should be easy - there is an owner_sid column containing an ID and a documented sys.sysowners table with an sid column. What could be easier?

    Code:
    SELECT  *
    FROM    sys.owners
    Quote Originally Posted by SQL @*#%ing Server
    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'sys.owners'.
    I've tried all sorts of variations in all different databases. I am sysadmin.

    These all work splendidly:
    Code:
    SELECT  OBJECT_NAME(object_id), *
    FROM    sys.columns
    WHERE   OBJECT_NAME(object_id)    = 'sysowners'
    
    SELECT  SCHEMA_NAME(schema_id), name, type_desc
    FROM    sys.objects
    WHERE   name    = 'sysowners'
    So it is there in the system catalogue and documented but I can't access it.

    Any thoughts? Any alternatives? There are methods to set the owner of a job but not one I can find to get the owner of the job.
    Last edited by pootle flump; 03-16-10 at 07:28.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Right - after running sp_help_job and poking about in traces I have got to this point:
    Code:
    SELECT  dbo.SQLAGENT_SUSER_SNAME(owner_sid)
          , SUSER_SNAME(owner_sid)
          , *
    FROM    dbo.sysjobs
    The undocumented SQLAGENT_SUSER_SNAME returns the result of SUSER_SNAME if owner_sid is greater than zero (it is VARBINARY(65)) and the SQL Agent account if it is not.

    I haven't solved what has happened to sysowners, but I can at least get the information I need.

    I forgot to mention, this was all on 2005 and 2008 standard & enterprise.

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Don't know if these snippets are of any use, but will give job owner at the lower level.

    Code:
    select distinct j.*
    from msdb.dbo.sysjobs j
    inner join msdb.dbo.sysjobschedules s on j.job_id = s.job_id
    
    select o.name
    from sysobjects o
    inner join syscolumns c on o.id = c.id
    where c.name like 'owner_sid'
    
    select u.*,v.*
    from sysjobs_view v,sysusers u
    where owner_sid = u.sid

  4. #4
    Join Date
    Feb 2007
    Posts
    38
    Hi

    This link may give you answer.
    SIDs and IDs

    So try this.

    Select a.name [JobName],a.owner_sid, b.sid, b.name [OwnerName]
    From msdb..sysjobs a
    Join sys.server_principals b
    on a.owner_sid=b.sid

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I'm having a hard time pulling myself off of the 2000 way of querying the system catalog.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - sorry guys - I do need to read that link....

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just noticed this and thought I would post - the answer to the original question was in the link I put in the opening post all along:
    To bind to a system base table, a user must connect to the instance of SQL Server by using the dedicated administrator connection (DAC). Trying to execute a SELECT query from a system base table without connecting by using DAC raises an error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •