Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: granting permissions on set of tables

    I have 500 tables in my Db.In those some tables name starts with abc (abc_emp,abc_dept) and rest of the tables name start with xyz(xyz_emp,xyz_transactions).I wanted to give select,insert,update and delete permissions for an user on the tables which starts with abc.
    How can i do that in a much easier and sophisticated way.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What I would probably do is create a role and call it something like abc_tables. Then generate the commands to grant the permissions on those tables with a query like:
    Code:
    select 'grant select, update, insert, delete on ' + name + ' to abc_tables'
    from sysobjects
    where type = 'U'
      and name like 'abc%'
    Run the resulting grant statements, then add the user to the role. The beauty of this is, if you have to grant the same permissions to another user, then you have most of the work already done.

Posting Permissions

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