Am I doing this right?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve - 18 May 2006 19:41 GMT

I have a new user that will eventually be another admin on our SQL Server 200
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?

Reply to this Message

Sue Hoegemeier - 19 May 2006 02:17 GMT

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

>I have a new user that will eventually be another admin on our SQL Server 200
>installation. I want to give him permission to stop and Restart Scheduled
[quoted text clipped - 6 lines]
>and then selecting db_denydatawriter do what I want to do, or will the also
>selected SystemAdministrator priviledge override this altogether?

Reply to this Message

Steve - 19 May 2006 21:11 GMT

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 and
do liuttle or nothing else? I am stumped on this and it seems like it
*ought* to be easy!

Steve

> A deny for a sysadmin won't do anything. Members of the
> sysadmin role bypass security checks - sysadmins are able to
[quoted text clipped - 12 lines]
> >and then selecting db_denydatawriter do what I want to do, or will the also
> >selected SystemAdministrator priviledge override this altogether?

Reply to this Message

Sue Hoegemeier - 22 May 2006 01:07 GMT

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

>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 and
[quoted text clipped - 19 lines]
>> >and then selecting db_denydatawriter do what I want to do, or will the also
>> >selected SystemAdministrator priviledge override this altogether?

Reply to this Message

rahulpt - 26 Jul 2006 05:12 GMT

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 something
on this?

Regards

Signature

Rahul

> There just isn't that level of granularity with security
> under SQL Server 2000. That changes in SQL Server 2005
[quoted text clipped - 30 lines]
> >> >and then selecting db_denydatawriter do what I want to do, or will the also
> >> >selected SystemAdministrator priviledge override this altogether?

Reply to this Message

Sue Hoegemeier - 27 Jul 2006 17:17 GMT

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

>Hi Sue/Steve
>
[quoted text clipped - 7 lines]
>
>Regards

Reply to this Message