Re: Grant, Revoke, Deny

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/12/05 

Date: Mon, 12 Sep 2005 14:33:35 -0500

Permissions are cumulative and DENY takes precedence. REVOKE removes GRANT
and DENY permissions previously assigned. Because all users are members of
public, you should DENY permissions to public only when you want to prohibit
access by all database users.

'sa' is a member of the sysadmin fixed server role and permissions are not
checked for sysadmin role members. I believe you are mistaken when you say
that you prevented 'sa' from accessing the table.

One method to allow Apps users to SELECT from the table and prevent other
'db_datareader' and 'db_datawriter' access:

1) GRANT SELECT ON YourTable TO Apps

2) DENY INSERT, UPDATE, DELETE ON YourTable TO Apps

3) create a new role to prevent access (e.g. 'DenyCertainTables')

4) DENY ALL ON YourTable TO DenyCertainTables

5) add all 'db_datareader' and 'db_datawriter' role members, except Apps
role members, to the 'DenyCertainTables' role. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephen Costanzo" <sxcostanzo@hotmail.com> wrote in message 
news:uqHH2W8tFHA.3528@TK2MSFTNGP15.phx.gbl...
> Context: SQLServer 2000
>
> I have a single table in the database that I want role "Apps" to have 
> SELECT
> access only, so using the UI, I find the table and check the SELECT column
> for the table and choose the X (to deny) for INSERT, UPDATE, DELETE.
>
> Next, I want to ensure that no one that has public rights to the database
> can do anything with this table. First time through I put an X (to deny) 
> for
> all four permissions, which prevented everyone (including sa) from
> selecting, etc. I found this to be 'by design' behavior so I using the 
> Query
> Analyzer to issue the REVOKE method on the table for all 4 items. sa was
> then able to still do everything.
>
> My problem is that anyone in the db_datawriter role (or db_datareader) can
> still write (read) from the table. I am unable to reassign permissions to
> special roles.
>
> I guess I could not use the special role for my datareaders/writers but 
> the
> advantage of these roles is that I don't have to continuously go and grant
> permissions. If this is my only option in this scenario, then I guess I 
> will
> have to go with it - I was just hoping there was something more elegant.
>
> Thanks in advance
>
>

Relevant Pages

  • Re: Folder permissions - deny users, allow administrator
    ... members of Users are not to have the same permissions for "working" ... deny everything but read access to Users members in "completed". ... With those permissions there will be no explict permissions on ...
    (microsoft.public.security)
  • Re: Permissions problems
    ... Yes administrators can be members of the users group and certainly will be ... Instead of deny permission just ... remove those permissions that you do not want the groups to have. ...
    (microsoft.public.windowsxp.security_admin)
  • Re: Overlapping Permissions
    ... Do any users or groups have deny set on the ... Permissions are cumulative but deny will take precedence. ... >There is another group, ProductManagers, who are also members of the above ... and added the ProductManagers to it. ...
    (microsoft.public.sqlserver.security)
  • Re: Overlapping Permissions
    ... that database? ... And members of the ProductMgmt role can select, ... Do any users or groups have deny set on the ... >> Permissions are cumulative but deny will take precedence. ...
    (microsoft.public.sqlserver.security)
  • Re: how to restrict users to search in their own Organizational Unit
    ... I also want to say that in fact you shouldn't deny the read permission to anyone and this scenario the MOSS Administrators or who is responsible for Add users to Your Sites should be carefull when performing this action. ... Now, because you're dealing with many users, my recommendation is to create THE NECESARY Security Groups in each OU and related them with your MOSS2007 existing security groups, in future when someone creates some user, you just have to add that user to the necessary group and that user will be given the necessary permissions. ... decided a script can make it possible to accomplish, ... > If I need to create a security group per OU and then add all users ...
    (microsoft.public.windows.server.active_directory)