Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Bulk Grant Select failing

    Hi there
    I'm still finding my way in SQL server so the problem might be very simple (hopefully...).
    Would anybody have any idea why:

    grant select on table1 to ReadGroup

    works fine, and

    grant create table to ReadGroup

    works fine, yet

    grant select to ReadGroup

    results in
    Server: Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'to'.
    ?

    Any help would be immeasurably appreciated
    Cheers!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    Hi
    I think you need to specify what table they are being granted select ON...
    ie.
    GRANT SELECT
    ON table
    TO user
    GO

    add them to db_datareader role if you want database wide select ...
    des

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    if u are looking for granting select permissions to all the user tables
    then :


    declare @table varchar(100)
    select [name] into #temp from sysobjects where xtype='u'
    while exists (select * from #temp)
    begin
    select top 1 @table=[name] from #temp
    exec ('Grant select on '+@table+' to ReadGroup')
    delete from #temp where [name]=@Table

    end
    drop table #temp

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Thanks for this - Yep, wanted to set permissions to over 300 tbales in one go. Wondered if the failure was due to not specifying object types but wasn't sure how to - thanks for your help guys

    Cheers
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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