Re: Windows Group Access to SQL Scheduled Tasks - SQL 2000
- From: Sue Hoegemeier <Sue_H@xxxxxxxxxxxxx>
- Date: Wed, 18 Oct 2006 19:57:37 -0600
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:
- Prev by Date: Re: permissions for SQL admin
- Next by Date: Re: Preventing Enterprise Manager access from Windows users
- Previous by thread: Re: permissions for SQL admin
- Next by thread: Re: Windows Group Access to SQL Scheduled Tasks - SQL 2000
- Index(es):
Relevant Pages
|
