Re: SQL job owner and right

Yes Russell just corrected me on that. I never tried that but at the time it seemed a perfect solution:(. Russell did offer a compromise I suppose. If you create a Windows login that each of these people can use when editing jobs you can make it the owner. But you still have the same issue of security that I mentioned earlier. If you trust these users enough to let them do anything they want to any of those jobs then you can trust them to login with that account when editing jobs. Otherwise they can only edit their own.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Lynn" <Lynn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:BDC699D7-8EC0-4AF2-A0C2-09FFF096EE86@xxxxxxxxxxxxxxxx
Andrew,

Thank you for your suggestion. I added a window group in SQL security and
try to assign the job owner to this group, but the window group is not
allowed to be the owner of the job by design. I got the error message when I
assign group to job owner

The specified'@owner_login_name' is invalid (valid values are returned by
sp_helplogins [excluding Windows NT groups]).

Please help. Thanks.
--
Best Regards,

Lynn


"Andrew J. Kelly" wrote:

By "Operate and Change" the job do you mean edit what the job does or just
when it runs? If you want to be able to edit other peoples jobs you have to
be sa. Otherwise you run the risk of someone who is not able to access or
modify data being able to do so thru editing a job that is owned by someone
who is authorized. That defeats the purpose of the security roles
altogether. You can't have it secure yet open at the same time. If you want
more than 1 user to edit the same job then you should create a Windows group
that both windows users belong to and have that own the jobs. This again
assumes both users will have the same permissions or they should not be in
the same group.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Lynn" <Lynn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4A5968F0-2DE8-4C5B-8689-77907FB320E1@xxxxxxxxxxxxxxxx
> Sorry. I just confirmed that we only have SQLAgentUserRole. That's > the
> reason I couldn't see the job which is created by account A.
>
> Again, more than 2 people will manage same jobs for application, but > not
> the
> jobs related to server maintainance. Reader and Operator role won't
> fulfill
> this right as well. What's the best practice for this situation.
>
> Thank you again.
> -- > Best Regards,
>
> Lynn
>
>
> "Lynn" wrote:
>
>> We are in SQL 2005 and my account is already granted all those three
>> roles
>> and account A also has these three roles. But, I can't see the job >> that
>> account A created.
>>
>> What's the best practice to archive -- multiple accounts can manage >> SQL
>> job
>> without sysadmin right?
>>
>> Thank you again.
>> -- >> Best Regards,
>>
>> Lynn
>>
>>
>> "Andrew J. Kelly" wrote:
>>
>> > If you are using SQL2000 then you need to be sa. In 2005 there are 3
>> > new
>> > roles that define the abilities of non-owners in SQL Agent. Check >> > out
>> > these
>> > in BooksOnLine for more details:
>> >
>> > SQLAgentUserRole
>> >
>> > SQLAgentReaderRole
>> >
>> > SQLAgentOperatorRole
>> >
>> > -- >> > Andrew J. Kelly SQL MVP
>> > Solid Quality Mentors
>> >
>> >
>> > "Lynn" <Lynn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> > news:1AE16035-2A1A-4252-A978-196C53ED16C4@xxxxxxxxxxxxxxxx
>> > > Hi,
>> > >
>> > > A user has right to create and operate a SQL job, but only limited >> > > to
>> > > the
>> > > jobs that this user is created. Without granting sysadmin right, >> > > how
>> > > to
>> > > grant a user to be able to operate and change the job which is
>> > > created by
>> > > other users?
>> > >
>> > > Thank you.
>> > > -- >> > > Best Regards,
>> > >
>> > > Lynn
>> >
>> >



.

Relevant Pages

  • Re: SQL job owner and right
    ... By "Operate and Change" the job do you mean edit what the job does or just when it runs? ... and account A also has these three roles. ... > SQLAgentUserRole ... > Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.security)
  • Re: SQL job owner and right
    ... I just confirmed that we only have SQLAgentUserRole. ... and account A also has these three roles. ... What's the best practice to archive -- multiple accounts can manage SQL job ... Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.security)
  • Re: backup-how to find mapped drive
    ... Andrew J. Kelly SQL MVP ... > I tried that but i got a message saying account could not be verified do u ... Mapped drives should never ... >> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Operating System Command (CmdExec) will not run from Job Agent
    ... Andrew J. Kelly SQL MVP ... >> Andrew J. Kelly SQL MVP ... >>>I created a proxy account, from what I understand SQL Server should only ... >>> to use the account if the job owner is not a member of sa. ...
    (microsoft.public.sqlserver.dts)
  • Re: bcp error
    ... Andrew J. Kelly SQL MVP ... > server and service account both have permission to then it won't run. ...
    (microsoft.public.sqlserver.security)