Can''t revoke permissions from views and tables

• Serge Babeshko

  Newbie

  Points: 2

  More actions

  February 4, 2007 at 10:22 pm

  #92179

  Hi there,

  I'm trying to revoke the permissions from public role on EnterpriseOne database:

  Revoke all on [owner].[objectname] from public

  and I've got message: "The command(s) completed successfully."

  But when I executed sp_helprotect '[owner].[objectname]', 'public' command it looks like this role still has all grands on this objects:

  Owner Object      Grantee      Grantor         ProtectType    Action Column

  ------ ------        ---------- ------------ -------------- ----------- ------ ----------

  owner objectname public        Grantor        Grant              Delete .

  owner objectname public        Grantor        Grant              Insert .

  owner objectname public        Grantor        Grant              Select (All)

  owner objectname public        Grantor        Grant              Update (All)

  (4 row(s) affected)

  When I try to deny access first "DENY DELETE ON  [owner].[objectname] TO public" it works fine but after I run 'revoke' command again the all permissions became 'Grant' back.

  I did try to do this from Enterprise manager but the permissions are still there...

  This happened on about 20 objects in this database, all the rest are fine.

  I'm connection under 'sa' login.

  SQL Server 2000 SP4 Ent.

  What could it be?

  thanks

• Serge Babeshko

  Newbie

  Points: 2

  More actions

  February 7, 2007 at 5:03 pm

  #688438

  I've fixed this.

  sp_configure 'allow updates', 1

  reconfigure WITH OVERRIDE

  delete from syspermissions where grantee = 0 and id = <objectID>

  sp_configure 'allow updates', 0

  reconfigure WITH OVERRIDE

  thanks all

• Jeff Moden

  SSC Guru

  Points: 1004641

  More actions

  February 7, 2007 at 9:35 pm

  #688450

  Thanks for posting your own solution... Lordy, I hope someone comes up with a different way... I really have bad gut feeling about updating system tables...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)