I am having problems with this issue and I think it might be a bug with
SQL Server 2000. If you add a user to the targetserverrole in the msdb
db, it will allow him/her to see jobs and dts packages. However, on
jobs that are still executing, it does not show the running status.
Instead, it will say the jobs are not running when it's executing. The
only role I have found that allows the user to see the status of jobs
executing is the sa. I do not want to grant sa just for this purpose.
Any input?

Jules

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The targetserverrole will not influence DTS packages. The fact that Guest
is a member of public in msdb does and the permissions on the DTS stored
procs.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Jules Bui" <jbui (AT) cfsloans (DOT) com> wrote

Allan,
Do you have any ideas on how to get the status of running jobs to show
in the jobs GUI w/o having to grant sa? That is the main question
behind this posting.

Jules

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

"sp_help_job" calls "sp_get_composite_job_info" to get the execution status
which tests for sysadmin or job owner. It passes this into an xp
"master.dbo.xp_sqlagent_enum_job" which will determine I think the run
status

In your case I believe that if you do not own the job or are not a sysadmin
it will return a status of 4 for the job

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Jules Bui" <jbui (AT) cfsloans (DOT) com> wrote

Allan,
Thanks, but that is the problem that I'm running into, unless I grant
certain users sa access, they will not see the status of jobs still
executing unless they are the job owner.

Jules

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!