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