Help with SQL Agent Security

Hi,
I want to be able to allow certain non (SA) users to see SQL Agent
jobs. Whilst I am aware that all I need to do is add
them to the 'TargetServersRole'(and fix any sp3 changes), this does
not allow them to see the status of any jobs. This is because when
they
look at the jobs, sp_get_composite_job_info runs a check
to see if they are sysadmin before running
xp_sqlagent_enum_jobs. If they aren't sysadmin the
status gets returned as not running (4).
You will note that all jobs have a status of 'Not Running' even though
some are definitely running ie. replication tasks
The following posting is exactly what I have an issue with and would
like to resolve.
http://groups.google.com.au/groups?...80a%2540phx.gblThere is no supported way to do this. In terms of using
TargetServerRole, that's not officially supported either.
Using the role isn't documented as it's used by SQL Server
for multi-server administration and it's permissions has
changed through some of the service packs.
Essentially, the behavior you are seeing is by design.
-Sue
On 17 Feb 2005 20:50:18 -0800, jharalam@.colesmyer.com.au
(Jharalam) wrote:

>Hi,
>I want to be able to allow certain non (SA) users to see SQL Agent
>jobs. Whilst I am aware that all I need to do is add
>them to the 'TargetServersRole'(and fix any sp3 changes), this does
>not allow them to see the status of any jobs. This is because when
>they
>look at the jobs, sp_get_composite_job_info runs a check
>to see if they are sysadmin before running
>xp_sqlagent_enum_jobs. If they aren't sysadmin the
>status gets returned as not running (4).
>You will note that all jobs have a status of 'Not Running' even though
>some are definitely running ie. replication tasks
>The following posting is exactly what I have an issue with and would
>like to resolve.
>http://groups.google.com.au/groups?...80a%2540phx.gbl|||If using windows NT Authenication, make sure that you log into SQL
Server exectaly how your UseriD is defined in Windows in the CORRECT
case
e.g in Windows your user is
DOMAIN\Your.Name
sign on as DOMAIN\Your.Name not DOMAIN\your.name there is an Extended
Stored Procedure that does some sort of binary User Name comparison
to check your credentials and this can sometimes prevent you from
seeing the Job Status as it evaluates: Your.Name <> your.name
as false.
This is of course a BUG! Opps Feature of the system
Paul

help with SQL Agent job status

I have been using XP_SQLAGENT_ENUM_JOBS to get info on my jobs to see where
they are at in the process. It doesnt seem to always becorrect in its output,
however. XP_SQLAGENT_ENUM_JOBS is an undocumented proc inside of sp_help_jobs
and is used extensivly internally to get sql agent job info.
I am creating a web status page that is trying to emulate the
EM-->management-->sql server agent -->jobs status field. I would like to
reproduce the same info myself instead of relying on this sproc.
Thanks in advance!!
The SQL Server Agent jobs run in a job cache. Statuses are
written to the tables after the fact so you can't query
system tables. You can use sp_help_job as this executes the
extended stored procedure xp_sqlagent_enum_jobs to obtain
the current job status from the job cache.
Enterprise Manager uses sp_help_job.
-Sue
On Wed, 11 Aug 2004 13:49:02 -0700, "Carl Henthorn"
<CarlHenthorn@.discussions.microsoft.com> wrote:

>I have been using XP_SQLAGENT_ENUM_JOBS to get info on my jobs to see where
>they are at in the process. It doesnt seem to always becorrect in its output,
>however. XP_SQLAGENT_ENUM_JOBS is an undocumented proc inside of sp_help_jobs
>and is used extensivly internally to get sql agent job info.
> I am creating a web status page that is trying to emulate the
>EM-->management-->sql server agent -->jobs status field. I would like to
>reproduce the same info myself instead of relying on this sproc.
>Thanks in advance!!
sql

help with SQL Agent job status

I have been using XP_SQLAGENT_ENUM_JOBS to get info on my jobs to see where
they are at in the process. It doesnt seem to always becorrect in its output
,
however. XP_SQLAGENT_ENUM_JOBS is an undocumented proc inside of sp_help_job
s
and is used extensivly internally to get sql agent job info.
I am creating a web status page that is trying to emulate the
EM-->management-->sql server agent -->jobs status field. I would like to
reproduce the same info myself instead of relying on this sproc.
Thanks in advance!!The SQL Server Agent jobs run in a job cache. Statuses are
written to the tables after the fact so you can't query
system tables. You can use sp_help_job as this executes the
extended stored procedure xp_sqlagent_enum_jobs to obtain
the current job status from the job cache.
Enterprise Manager uses sp_help_job.
-Sue
On Wed, 11 Aug 2004 13:49:02 -0700, "Carl Henthorn"
<CarlHenthorn@.discussions.microsoft.com> wrote:

>I have been using XP_SQLAGENT_ENUM_JOBS to get info on my jobs to see where
>they are at in the process. It doesnt seem to always becorrect in its outpu
t,
>however. XP_SQLAGENT_ENUM_JOBS is an undocumented proc inside of sp_help_jo
bs
>and is used extensivly internally to get sql agent job info.
> I am creating a web status page that is trying to emulate the
>EM-->management-->sql server agent -->jobs status field. I would like to
>reproduce the same info myself instead of relying on this sproc.
>Thanks in advance!!