Re: Am I doing this right?



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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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

"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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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?



.



Relevant Pages

  • Re: Public Role for SQL 2000
    ... public is the default role for all database users. ... their minimum permissions and privileges. ... Security of Microsoft SQL Server 2005 Administrator's Pocket Consultant: ...
    (microsoft.public.sqlserver.security)
  • Re: How to Identify a Users Permissions to SQL Server Tables via VB 6.0 Application
    ... > SQL Server will be setup using Windows Security ... > some feedback on the form as to what type of permissions he has while ... > For example, Michael opens a particular form, code in the background ...
    (microsoft.public.sqlserver.security)
  • Re: Permission to restore databases and access them without being
    ... dbcreator to the login so that you can restore the databases. ... " in SQL Server 2005 Books Online: ... assign your wanted permissions such as db_reader/db_writer ...
    (microsoft.public.sqlserver.security)
  • How to Identify a Users Permissions to SQL Server Tables via VB 6.0 Application
    ... let's say I'm user "Michael" that's sitting down at my ... SQL Server will be setup using Windows Security ... some feedback on the form as to what type of permissions he has while ...
    (microsoft.public.sqlserver.security)
  • Re: Hiding database
    ... Jasper Smith (SQL Server MVP) ... >>>Can someone explan how to hide databases in enterprise manager. ... >> permissions are granted to these users. ... > have access rights to one of them. ...
    (microsoft.public.sqlserver.tools)