Am I doing this right?

I have a new user that will eventually be another admin on our SQL Server 20
0
installation. I want to give him permission to stop and Restart Scheduled
jobs, yet not have permission to insert/update or delete in specific
databases until he gets more experience. The only predefined server role
that allows access to scheduled jobs is SystemAdministrator, but that also
gives permissions everywhere to everything.
Will creating a User in each database I want to protect based on his login,
and then selecting db_denydatawriter do what I want to do, or will the also
selected SystemAdministrator priviledge override this altogether?A deny for a sysadmin won't do anything. Members of the
sysadmin role bypass security checks - sysadmins are able to
do anything on the server and in any databases.
-Sue
On Thu, 18 May 2006 11:41:03 -0700, Steve
<Steve@.discussions.microsoft.com> wrote:

>I have a new user that will eventually be another admin on our SQL Server 2
00
>installation. I want to give him permission to stop and Restart Scheduled
>jobs, yet not have permission to insert/update or delete in specific
>databases until he gets more experience. The only predefined server role
>that allows access to scheduled jobs is SystemAdministrator, but that also
>gives permissions everywhere to everything.
>Will creating a User in each database I want to protect based on his login,
>and then selecting db_denydatawriter do what I want to do, or will the also
>selected SystemAdministrator priviledge override this altogether?|||Thanks for the reply. So then how in the world can I create a login that
allows the user to access, start and stop SQL Server Agent scheduled jobs an
d
do liuttle or nothing else? I am stumped on this and it seems like it
*ought* to be easy!
Steve
"Sue Hoegemeier" wrote:

> A deny for a sysadmin won't do anything. Members of the
> sysadmin role bypass security checks - sysadmins are able to
> do anything on the server and in any databases.
> -Sue
> On Thu, 18 May 2006 11:41:03 -0700, Steve
> <Steve@.discussions.microsoft.com> wrote:
>
>|||There just isn't that level of granularity with security
under SQL Server 2000. That changes in SQL Server 2005
though.
The only way to get close is by using an undocumented role
in msdb - TargetServer role. However, the permissions for
this role changes depending on the service pack level and
you'd have to be running at SP 2 or lower to get close to
what you are asking for.
-Sue
On Fri, 19 May 2006 13:11:03 -0700, Steve
<Steve@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the reply. So then how in the world can I create a login that
>allows the user to access, start and stop SQL Server Agent scheduled jobs a
nd
>do liuttle or nothing else? I am stumped on this and it seems like it
>*ought* to be easy!
>Steve
>"Sue Hoegemeier" wrote:
>|||Hi Sue/Steve
I feel with Sp3 & higher we can achieve these by modifying default Security
Permissions asigned to TargetServer Role. But only quetion is what Security
Risk we have when we enable this option .i.e. TargetServer Role.
I am in scenario where we want to view SQL job history running on more tahn
100 Servers & we don;t have master server scenario. Can you suggest somethin
g
on this?
Regards
--
Rahul
"Sue Hoegemeier" wrote:

> There just isn't that level of granularity with security
> under SQL Server 2000. That changes in SQL Server 2005
> though.
> The only way to get close is by using an undocumented role
> in msdb - TargetServer role. However, the permissions for
> this role changes depending on the service pack level and
> you'd have to be running at SP 2 or lower to get close to
> what you are asking for.
> -Sue
> On Fri, 19 May 2006 13:11:03 -0700, Steve
> <Steve@.discussions.microsoft.com> wrote:
>
>|||No...I wouldn't suggest that and don't think it would work.
Additionally using TargetServer role is undocumented as I
already posted.
I'd look at writing your own application, front end to
manage you needs.
-Sue
On Tue, 25 Jul 2006 21:12:02 -0700, rahulpt
<rahulpt@.discussions.microsoft.com> wrote:

>Hi Sue/Steve
>I feel with Sp3 & higher we can achieve these by modifying default Security
>Permissions asigned to TargetServer Role. But only quetion is what Security
>Risk we have when we enable this option .i.e. TargetServer Role.
>I am in scenario where we want to view SQL job history running on more tahn
>100 Servers & we don;t have master server scenario. Can you suggest somethi
ng
>on this?
>Regards