Re: "REVOKE ALL FROM public" not working

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 10/21/03

  • Next message: Sue Hoegemeier: "Re: Service Account for xp_cmdshell & osql" 
    Date: Tue, 21 Oct 2003 09:23:59 -0700

    Hi Peter

    GRANT/REVOKE/DENY statements in TSQL have 2 flavors. Statement permissions
    include things like permission to create a table, a view, a procedure, etc.
    Object permissions include insert/update/delete on tables and views, or
    execute on stored procedures.

    Unfortunately, one permission command cannot be used for all object
    permissions.

    The REVOKE ALL only applies to the statement permissions, so your statement
    would remove any grants of permission to CREATE objects. It does not remove
    any permissions to access individual objects. That needs to be done one
    object at a time:

    REVOKE ALL ON table1 FROM public
    REVOKE ALL ON table2 FROM public

    REVOKE ALL on proc1 FROM public
    etc.

    You could build a script for this by selecting all object names from the
    INFORMATION SCHEMA VIEWS, but it still would need to be executed on each
    object. 

    --
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Peter Daniels" <Peter.Daniels@cts.cendant.com> wrote in message
news:3b8352e8.0310210816.5e9c2aa7@posting.google.com...
> I have a database in which the public role has been granted SELECT,
> INSERT, UPDATE, DELETE on all user tables and EXECUTE on all sprocs.
> I thought that
>
> REVOKE ALL FROM public
>
> ...would do the trick.  It said that "The command(s) completed
> successfully.", but EM shows no change in permissions nor does
> sp_helpprotect @username='public'.
>
> Also, I have checked to make sure that I am in the DB that I want to
> change perms in.  The script with the REVOKE includes a USE <MyDB> GO.
>  Also I am a member of sysadmin, so it isn't a permission issue.
>
> TIA,
>
> -Peter
  • Next message: Sue Hoegemeier: "Re: Service Account for xp_cmdshell & osql"

    Relevant Pages

    • Re: cant block delete permissions
      ... If you have any roles apart from db_datareader and db_datawriter revoke ... Now execute the delete statatement on that table. ... opened the table's 'manage permissions' dialog and explicitly denied delete ... We looked at her database role membership and saw that she was a member of ...
      (microsoft.public.sqlserver.security)
    • Re: SP Permission Inheritance and Table Creation
      ... table serves as a 'static index' that we generate for quicker access to ... > The same CREATE statement permissions apply regardless of how the CREATE ... >>> referenced objects are not checked. ...
      (microsoft.public.sqlserver.security)
    • Re: SP Permission Inheritance and Table Creation
      ... The same CREATE statement permissions apply regardless of how the CREATE ... Can you elaborate on why users need to execute DDL from within your app? ... >> referenced objects are not checked. ...
      (microsoft.public.sqlserver.security)
    • Re: permissions for new user to use stored procedure
      ... you didn't need to revoke or deny anything on the ... since a user has no permissions on an object by default. ... If I create stored procedures to select from, ... update and delete from the table do I only have to give execute ...
      (microsoft.public.sqlserver.security)
    • Solaris 10 autofs directory permissions - Solution
      ... the fact that my map file has 755 permissions not 644. ... If the execute permission is set, it becomes an executable map which is ... map is expected to return the content of an automounter map ...
      (SunManagers)