Post reply

group access to modify jobs

  • July 25, 2012 at 11:48 am

    #392828

    I've just migrated from sql 2000 to sql 2008 R2 and lost some abilities for my developers. In sql 2000 they had an access to the jobs of each other and could modify them. The only problem was that a job owner has been updated after modification to the last modifier's login.

    In sql 2008 they can modify only their own jobs, that makes problems. BOL-2008 says "Unless you are a member of the sysadmin fixed server role, you can only modify jobs that you own".

    Is there a way to allow a developer to modify other's job, except including him to "sysadmin" role?

    All devs are logged to sql 2008 under their windows logins, and all users are members of the AD group <DOMAIN>\SQL_developers that is a login in sql security.

  • July 25, 2012 at 12:03 pm

    #1517554

    There are several database roles in MSDB which should facilitate this.

    http://msdn.microsoft.com/en-us/library/ms188283.aspx

  • July 25, 2012 at 12:06 pm

    #1517556

    If this is a production environment all changes should go thru the DBA. The DBA is the one that will get the call when the job fails.

  • July 25, 2012 at 1:45 pm

    #1517616

    Thanks, but it doesn't work for me. A member of SQLAgentOperatorRole can create/modify/delete the owned jobs only, as an article says and as I tested.

    In my environment, developer1 should be able to update a job owned by developer2 or even sa.

    I have the weak hope I can play somehow with the fact that developer1 and developer2 are members of the domain group "sql_developers" that is a principal in a sql server. Is it possible to make a job owner the group, not the person?

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

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