Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    Smoggy Leather Burnt Skin City, California
    Posts
    3

    Unanswered: Is there a way to grant to multiple tables?

    Hi all,

    Is there a way to grant SELECT to multiple tables but not whole database in TSQL of MS SQL? ex: grant to table1, table 9, table100, ... rather then grant to whole database database by giving him/her db_datareader.

    I'm trying to limit what a certain account can select on.

    This work:

    grant SELECT
    on Table1
    to webuser

    grant SELECT
    on Table9
    to webuser
    ...repeat for other tables

    The above work...but too tiring after 50 tables

    I already test try:

    grant SELECT
    on Table1, Table9, Table100
    to webuser

    * but it doesn't like that...syntax error.

    Any ideas? Or are there any better way to deal with this? Thanks.

  2. #2
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    Try this way of creating your script..

    SELECT 'GRANT SELECT ON '+name+' TO webuser;'
    from sysobjects
    where type = 'U'

    or if you want to include views as well
    where type in ('U', 'V')

    Then just copy your results window into a query window and away you go..

  3. #3
    Join Date
    Jun 2003
    Location
    Smoggy Leather Burnt Skin City, California
    Posts
    3
    Wow, that's cool

    It sure help but it return too much.

    How can I narrow it down to just any table name that begins with some prefix such as test_ ?

    ex: test_table1 , test_table2

    Thanks.

  4. #4
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    Try this

    SELECT 'GRANT SELECT ON '+name+' TO webuser;'
    from sysobjects
    where type = 'U'

    and name like 'test_%'

  5. #5
    Join Date
    Jun 2003
    Location
    Smoggy Leather Burnt Skin City, California
    Posts
    3
    Hey blamar!!

    That was what I need. You da man! Thanks.

Posting Permissions

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