Win Group vs Win User and Jobs

Hi

I have just started at a new company and one of my first task is to tighten
access to the SQL Servers.

I have a group of Developer working on a test server.  This server contains
a number of databases that are used by other groups.  So far I have gotten
them away from SQL standard accounts and Windows user accounts.  In place, I
am now requiring access to using Windows Groups.  Since this is a test
server (and database), I have assigned this group to the DB_Owner role over
the appropriate database.

The problem I am having is when they want to run jobs.  They currently have
some jobs on the server.  However, when I tried to change the owner from the
standard or Windows user to the Windows Group, the groups do not show up
(SQL Server Agent -> Jobs -> Job -> Owner.  There is a dropdown box here) .
This is needed because now when the access the server, they do not see any
jobs.  I can reassign jobs to different owners, but it only individual
accounts are visible, not group accounts.

Am I missing something here.  They previously had SysAdmin rights, which is
something I am not going to give back to them.  (To many problems have
occurred with interference with other database users).

Any suggestions on overcoming this?  Do I just need to write a transact
command to change this ownership?

-Akinja

Hi Akinja,

Yes only logins which are members of the sysadmin role can reassign the
ownership of jobs. And you cannot reassign it to groups. However there
might be a dirty way (not preferable) by reassigning the group to the
dbo role on the msdb DB.

Regards,
-Manoj

Thanks

This will work.  This is only a test DB server.  Definitely not for
production.

Akinja


<manrajshekar@gmail.com> wrote in message
news:1114472730.050036.264550@l41g2000cwc.googlegroups.com...
> Hi Akinja,
>
> Yes only logins which are members of the sysadmin role can reassign the
> ownership of jobs. And you cannot reassign it to groups. However there
> might be a dirty way (not preferable) by reassigning the group to the
> dbo role on the msdb DB.
>
> Regards,
> -Manoj
>