Let's Talk Ownership (and SQL Jobs)

  • RSP

    SSCrazy

    Points: 2573

    Comments posted to this topic are about the item Let's Talk Ownership (and SQL Jobs)

  • Rich Mechaber

    SSChampion

    Points: 10935

    Great, thanks!

    I created a query to look for jobs not owned by SA, and discovered a small "gotcha". A LEFT JOIN would work, of course, but I don't really need it.

    -- Jobs not owned by sa.

    -- N.B.! DO NOT JOIN to Master.dbo.syslogins as some websites suggest.

    -- Using Windows integrated security means that the job owner may be an individual without a specific login, if the login

    -- was created for a Windows group.

    -- For example, when I create jobs, the owner defaults to OURDOMAIN\myusername, but there is no corresponding login.

    SELECT suser_sname(owner_sid) as OwnerName, *

    FROM msdb..sysjobs

    WHERE owner_sid<>suser_sid('sa')

    Rich Mechaber

  • DBA in Unit 7

    SSCarpal Tunnel

    Points: 4059

    Thanks for sharing.

    Just wanna add one note about the code for

    changing the Maintenance plan ownership :

    use msdb;

    go

    update dbo.sysssispackages

    set ownersid = (

    select sid from msdb.sys.syslogins where name = 'sa')

    where [name] = 'MaintenancePlanNameHere';

    It only works for SQL2008 because there is no dbo.sysssispackages in the MSDB in SQL2005. Use dbo.sysdtspackages90 in SQL 2005.

  • RSP

    SSCrazy

    Points: 2573

    Hi, Max: Thanks for pointing that out, b/c as a matter of fact, I included with the submission for both SQL 2005 and SQL 2008. I thought you could scroll to the SQL2K5 script, but guess it was omitted. (Steve?)

    Thanks all for your current (and future) comments!

    - RP

  • Jerry Hung

    SSChampion

    Points: 12908

    This runs beautifully with the Central Management Servers

    I fixed all maintenance plans and jobs on 20+ servers in 1 run 🙂

    --get the owners of the maintenance plans

    IF @@VERSION LIKE '%SQL SERVER 2008%' --SELECT @@VERSION

    BEGIN

    SELECT NAME

    ,description

    ,SUSER_SNAME(ownersid)

    --,*

    FROM msdb.dbo.sysssispackages -- sql 2008

    WHERE SUSER_SNAME(ownersid) <> 'sa'

    END

    ELSE

    BEGIN

    SELECT name

    ,description

    ,SUSER_SNAME(ownersid)

    FROM msdb.dbo.sysdtspackages90 -- sql 2005

    END

    --get the owners of the maintenance plans

    IF @@VERSION LIKE '%SQL SERVER 2008%' --SELECT @@VERSION

    BEGIN

    UPDATE msdb.dbo.sysssispackages

    SET ownersid = SUSER_SID('sa')

    WHERE SUSER_SNAME(ownersid) IN ('jerryhung')

    --AND [name] = 'MaintenancePlanNameHere' ;

    END

    ELSE

    BEGIN

    UPDATE msdb.dbo.sysdtspackages90

    SET ownersid = SUSER_SID('sa')

    WHERE SUSER_SNAME(ownersid) IN ('jerryhung')

    --AND [name] = 'MaintenancePlanNameHere' ;

    END

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005
  • UMG Developer

    SSChampion

    Points: 13482

    You really want all jobs owned by SA? Doesn't that sort of eliminate any idea of security? If a developer wants to change something they don't have permission for they just have to create a job to do it or to give themselves the permission to do it.

    I agree that maintenance plans and the like should be owned by SA, or another generic account with SA permissions, but I certainly won't be changing all of the jobs on our SQL servers to be owned by SA.

  • UMG Developer

    SSChampion

    Points: 13482

    Jerry Hung (11/19/2009)I fixed all maintenance plans and jobs on 20+ servers in 1 run 🙂

    Not quite that easy, I think you still have to manually change, save, change back, and save each maintenance plan for the owner change to be 100% complete.

  • tracy_d82

    SSC Journeyman

    Points: 79

    who knew it was that simple!

  • prance_43

    Valued Member

    Points: 69

    see i think it is more complicated instead of easy! there is no simple form to the code?

    Peter[/url]

  • simon.murin

    SSC-Addicted

    Points: 449

    I do agree the msdb.sys.syslogins shouldn't be used.

    Another way is to use the SUSER_SID function:

    SQL 2005 Maintenance Plans

    UPDATE

    [msdb].[dbo].[sysdtspackages90]

    SET

    [ownersid] = SUSER_SID('sa')

    SQL 2008 Maintenance Plans

    UPDATE

    [msdb].[dbo].[sysssispackages]

    SET

    [ownersid] = SUSER_SID('sa')

  • Rob Fisk

    SSCommitted

    Points: 1841

    Yeah. I'd be really worried about the security implications of this.

    I thought it was a great article highlighting the problems that can arise from personally owned jobs.

    Between it and the comments made I now have all the building blocks to write a job ownership transfer that can be run at point of departure of one user to transfer job ownership to the replacement.

    [font="Arial"]_______________________________________________________
    Change is inevitable... Except from a vending machine.[/font]

  • TomThomson

    SSC Guru

    Points: 104772

    Rob Fisk (11/24/2009)


    Yeah. I'd be really worried about the security implications of this.

    I thought it was a great article highlighting the problems that can arise from personally owned jobs.

    Pity it didn't highlight the problems that can arise from SA owned jobs as well. I really don't fancy having everything running with sysadmin privileges, whether it needs them or not.

    In fact I'm fairly tempted to say that no jobs at all should be owned by SA because (although you may want interdomain connections without having interdomain trust at NT level so that you have to have SQL logins as well as NT logins) there's no good reason why SA should ever be be able to login (it's easy to have a job that creates a random SA password and doesn't put it where any human can get it).

    Tom

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

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