Re: Windows Group Access to SQL Scheduled Tasks - SQL 2000

Keep in mind, going that route isn't documented and isn't
supported. The permissions for TargetServersRole have
changed in service packs.

-Sue

On Thu, 19 Oct 2006 08:02:03 -0700, SteveT
<SteveT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Thanks Sue for leading me in that direction. (and for reading my lengthy
explanation)

I experimented with the TargetServersRole in msdb, and indeed found that
adding my Windows group as a member allowed full enumeration of the SQL jobs.
But I also found degradated functionality since it appears that this role by
default "denies" access on several key procs. While not recommended, I went
and cleared the the "deny" parmissions on these objects, and now I get the
complete functionality I was looking for. This membership plus exec access
on several procs allows the target users full access (read and update) to all
jobs. In fact, when a target user changes an attribute in a job, the owner
is implicitly changed to this user, while the actual owner field in the job
remains "greyed out".

So it looks like I've found a workable solution. Thanks again!

.

Relevant Pages

  • Re: SQL Server Jobs - permissions for non-owners
    ... Russell Fields wrote in message ... > rights to looks at all jobs and job history. ... > in TargetServersRole cannot create or modify jobs. ...
    (microsoft.public.sqlserver.server)
  • Re: How Someone Can View Jobs in EM That He Does Not Own
    ... Yes, that works, but the downside is that a member of TargetServersRole ... >>he does not own any of the jobs. ... >>The MSDN Library says you can modify jobs if you're given ...
    (microsoft.public.sqlserver.security)
  • Help with SQL Agent Security
    ... If you have them in targetserversrole and a data reader ... Ray Higdon MCSE, MCDBA, CCNA ... >to see the status of any jobs. ...
    (microsoft.public.sqlserver.security)
  • Re: SA Security
    ... You can allow them to see jobs (definition and history) by adding them to ... the TargetServersRole in the msdb database. ...
    (microsoft.public.sqlserver.security)