Re: SQL job owner and right

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: Multi Domain Replication Security Issue
    ... Change the job owner to the local account with SQL system admin account ... >This could be a name resolution problem or a account problem. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Agent Jobs Failing
    ... Is changing the job owner to "sa" an option? ... Tibor Karaszi, SQL Server MVP ... > SQL Agent runs under a domain account with sysadmin rights ...
    (microsoft.public.sqlserver.server)
  • Re: How do people use SQL in Win authenticated mode only?
    ... If you do not want to use sa as the job owner, you can create another SQL ... login and make it the job owner. ... the context of the SQL proxy account so this account will need the ...
    (microsoft.public.sqlserver.server)
  • Re: user permission to run job only
    ... I was able to run the job after I assign the job owner to the user. ... > Hello SQL, ... > What is the error message that you are getting? ... > Aaron Weiker ...
    (microsoft.public.sqlserver.programming)