Re: Windows Group Access to SQL Scheduled Tasks - SQL 2000
- From: SteveT <SteveT@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 19 Oct 2006 08:02:03 -0700
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!
--
Thanks very much,
SteveT
"Sue Hoegemeier" wrote:
Basically, there isn't any documented, supported approach to.
this. As you found, they need to own the jobs or be
sysadmins. Managing jobs and seeing jobs are probably two
different things. To just view jobs, the undocumented
approach would be to add the users (or group) to the
TargetServer role in msdb. They can't do all that much as
members of this role but that's probably as close as you are
going to get without granting sysadmin.
-Sue
On Wed, 18 Oct 2006 08:53:02 -0700, SteveT
<SteveT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi,
Like many others, as a Production DBA I'm trying to migrate towards Windows
groups for as much SQL access as practical. This is giving me some headaches
when attempting to setup a Windows security group for our Logical/Design DBAs.
I've created a group that has just a couple server-wide roles assigned (only
Process Admin and Bulk Insert Admin), and will default to the following
database roles in all user DBs: db_datareader, db_datawriter, and
db_ddladmin. Since these users have been accustomed to being System Admins,
we are attempting to phase this in by allowing the above settings on
Production servers.
Since these users will require to continue (for now) managing some SQL Agent
jobs, I don't have any problem letting them see ALL jobs and working with
these jobs. Herein lies the problem, since you can not change pre-existing,
or for that matter, any new jobs to be owned by a group login, I can not find
a way to enable ALL jobs to be seen by these users unless they are elevated
to a System Admin server role. (now against our rules)
I've tried to provide extensive access to msdb objects and even tried
setting them as DBO on msdb, but this does nothing. The only scenario that
appears workable is to provide an exact copy of every job, each owned by each
specific user in the group! This is not ideal, nor is getting in the
business of changing job owners on demand whenever a different user needs to
view a particular job.
Has anyone found, or know of a way to allow ALL jobs to display when a user
clicks 'Jobs' under the SQL Agent tree in Enterprise Manager, when this user
is NOT a System Administrator? Or any way to allow 'Group' access to a set
of jobs?
- Follow-Ups:
- Re: Windows Group Access to SQL Scheduled Tasks - SQL 2000
- From: Sue Hoegemeier
- Re: Windows Group Access to SQL Scheduled Tasks - SQL 2000
- References:
- Re: Windows Group Access to SQL Scheduled Tasks - SQL 2000
- From: Sue Hoegemeier
- Re: Windows Group Access to SQL Scheduled Tasks - SQL 2000
- Prev by Date: Re: Message when click on login user
- Next by Date: Viewing effective Permissions by Role VS by Table
- Previous by thread: Re: Windows Group Access to SQL Scheduled Tasks - SQL 2000
- Next by thread: Re: Windows Group Access to SQL Scheduled Tasks - SQL 2000
- Index(es):
Relevant Pages
|
