Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005
    Posts
    22

    Unanswered: Permissions issue

    Hi,

    We have about 100 objects including tables and procedures,in our DDL scripts after creating the objects, we are also executing the following commands.

    grant all on <object_name> to public.
    go

    Since we are granting permissions to public all the objects are wide open, any user who can access the database can access all the tables.

    I want revoke these permissions from all the objects and grant permission to only dbo.
    Ex: grant all on <object_name> to <db_dbo>

    Can I revoke and grant permissions in two statements, one to revoke from all the objects another to grant permission on all objects to dbo .

    Please respond.

    Thanks,
    BSR.

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    If the tables/objects are owned by the dbo, then you don't need to grant anything - access to tables that you own is implicit.

    The revoke request has to be done on a per-object basis, but it is fairly easy to create such a script. For example, to revoke access from all tables:

    select "revoke all on " + name + char(10) + "go" from sysobjects where type='U'

    Save the output in a file, and then execute the file.

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •