Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

See and execute JOBS ONLY

8 views
Skip to first unread message

Roberto Carrasco

unread,
Sep 10, 2003, 10:58:13 AM9/10/03
to
Hi,
I'll be very thankfull if someone can help me !

I would like to know if is there a way to create a user
that can Manage all JOBS in the machine, see and execute
and monitor the job that is running ! I tried using -
ProcessAdmin Server role ! But I couldn't do it !


Thank's !

Dinesh.T.K

unread,
Sep 10, 2003, 11:14:26 AM9/10/03
to
Roberto,

Assign the user to the 'TargetServersRole' in msdb.Not sure, but I believe,
this role does not have permission to execute a job.Anyways, check it and
grant if the execute permission to sp_start_job is denied.

--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com

"Roberto Carrasco" <roberto_...@msn.com> wrote in message
news:0a1d01c377ab$f54a8340$a601...@phx.gbl...

Sue Hoegemeier

unread,
Sep 10, 2003, 11:34:44 AM9/10/03
to
Adding a user to TargetServerRole allows them to edit job
stops, delete jobs, run jobs they don't own, and other
activities to support MSX. I think they may only be
restricted from changing the job owner.

-Sue

Dinesh.T.K

unread,
Sep 10, 2003, 11:56:26 AM9/10/03
to
Sue,

Iam not sure if you are pointing to any mistake in what I mentioned or just
adding to it.That said..

>>..run jobs they don't own,

Roberto mentioned "that can Manage all JOBS ".

--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com

"Sue Hoegemeier" <Su...@nomail.please> wrote in message
news:21hulvopdqijqlag3...@4ax.com...

Sue Hoegemeier

unread,
Sep 10, 2003, 12:35:55 PM9/10/03
to
No...sorry just meant to add, just didn't thread or word it
correctly.
With quiete a few posts in here on the subject, a lot of
people assume that adding users to the role will give them
read only type of access and in addition to allowing
execution. It gives the users more than that. From the
subject, it kind of looked like Roberto could have wanted
the read only type of access where the users wouldn't be
allowed to change the jobs.

-Sue

Jasper Smith

unread,
Sep 10, 2003, 1:56:41 PM9/10/03
to
With SQL2000 SP3 they can't run,delete or modify jobs they don't own, it was
true prior to this (it may actually have been a post SP2 hotfix that changed
this) but in SP3 they are explicitly denied execute on things like
sp_start_job, sp_delete_job etc. The role still has update + delete on
sysdownloadlist and update on sysjobservers and systargetservers however so
if not using MSX then it's a very good idea to revoke these if the intention
is to give read access as modifying sysjobservers can prevent jobs from
running.

--
HTH

Jasper Smith (SQL Server MVP)

I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"Sue Hoegemeier" <Su...@nomail.please> wrote in message

news:7gkulv4rkftjos2q4...@4ax.com...

Sue Hoegemeier

unread,
Sep 10, 2003, 2:25:34 PM9/10/03
to
I didn't realize they changed this with SP3 - thanks for
pointing it out.
I like the change though - TargetServer role was really just
meant to support MSX and using it for other purposes isn't
necessarily a good idea. We just need a more granular
security model in SQL Server.

-Sue

Roberto Carrasco

unread,
Sep 10, 2003, 4:19:28 PM9/10/03
to
HI,

I tried to put the user as datareader in the msdb database
and I gave explicit access to sp_start_Job - sp_stop_job -
sp_sqlagent_refresh_job and add the user to the
TargetServerRole !

My operator only have to start the job specifying a step -
and see using refresh if the job is running ! That's all !
What do I HAVE TO DO

Thank's

C a r r a s c o

>.
>

Roberto Carrasco

unread,
Sep 10, 2003, 4:20:22 PM9/10/03
to
HI,

I tried to put the user as datareader in the msdb database
and I gave explicit access to sp_start_Job - sp_stop_job -
sp_sqlagent_refresh_job and add the user to the
TargetServerRole !

My operator only have to start the job specifying a step -
and see using refresh if the job is running ! That's all !
What do I HAVE TO DO

Thank's

C a r r a s c o

>.
>

Roberto Carrasco

unread,
Sep 10, 2003, 4:19:51 PM9/10/03
to
HI,

I tried to put the user as datareader in the msdb database
and I gave explicit access to sp_start_Job - sp_stop_job -
sp_sqlagent_refresh_job and add the user to the
TargetServerRole !

My operator only have to start the job specifying a step -
and see using refresh if the job is running ! That's all !
What do I HAVE TO DO

Thank's

C a r r a s c o
>-----Original Message-----

>.
>

Jasper Smith

unread,
Sep 10, 2003, 5:46:41 PM9/10/03
to
There is no way (without modifying system stored procedures) for a "normal"
user to see the running status of a job they don't own - you have to be a
sysadmin or job owner .Adding them to the TargetServerRole and giving it
exec on sp_start_job and sp_stop_job will allow them to stop and start jobs.
The problem with them seeing the status of a job is to be found in
sp_get_composite_job_info, and these lines

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

Whilst I would never advise changing system stored procedures,and it being
totally unsupported, hardcoding the sysadmin check will allow a "normal"
user to see the job status

--
HTH

Jasper Smith (SQL Server MVP)

I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"Roberto Carrasco" <roberto_...@msn.com> wrote in message
news:000c01c377d8$d65c7290$a301...@phx.gbl...

Roberto Carrasco

unread,
Sep 30, 2003, 1:11:18 PM9/30/03
to
Hi !

How can I make this modifications ??
The users owns the job that he is trying to refresh but
even this way he can't see the status of the job ! !

Please answer

Thank's

Roberto Carrasco

>.
>

0 new messages