Results 1 to 6 of 6

Thread: t-sql grant select help please

  1. #1
    Join Date
    Jan 2003
    Location
    England
    Posts
    3

    t-sql grant select help please

    Ok, this forum is new to me so here goes,

    I inherited an access 2 db of 200MB+ and have moved it onto SQL7, the database has 250+ tables with the front end accessing via a single .dbo account. I am by company default required to use Crystal Reports to display the information and have have created a 'read only' account (non dbo) for this purpose as db_datareader but it does not appear to have select rights against any tables.
    I am looking for a 'simple' way of allowing select against all the tables.

    eg: Grant Select on q4w_data.dbo.cm_hi_wk to [Q4W_Report]

    How about Grant Select on q4w_data.dbo.sysobjects to [Q4W_Report]?

  2. #2
    Join Date
    Dec 2002
    Posts
    181
    Neil,
    A user in the db_datareader group should be all you need. Can you run this from Query Analyzer and post the output:

    use q4w_data
    go
    sp_helpuser [Q4W_Report]


    Thanks,
    Jeff

  3. #3
    Join Date
    Jan 2003
    Location
    England
    Posts
    3
    Jeff,
    Thanks for your reply, the data requested is below. I placed the user in the datareader as would appear correct. I wonder if this is actually a fault with Crystal Reps as when I try viewing tables etc. using the Q4w_Report username, the tables are not visible unless I specifically issue / assign 'select' against them in the permissions section of SQL7. Hence I was feeling like I was getting nowhere fast.

    Notes: There is a Q4w_Reports database which contains the reports built from the Q4w_data historical records.
    Some of the reports are 'live' from the Q4w_data database and it is these which are the problem.
    ps. SQL7 has had SP4 applied and both before and after are the same.

    Anyway, here is the result of the query sp_helpuser.

    UserName : Q4w_Report
    GroupName: db_datareader
    LoginName: Q4w_Report
    DefDBName: Q4w_Reports
    UserID: 6
    SUserID: 7

    Thanks,
    Neil.

  4. #4
    Join Date
    Dec 2002
    Posts
    181
    I think that might explain it if I'm reading this right. db_datareader is database specific, not system wide. Put the user in that role for both dbs and see what happens.

    Jeff

  5. #5
    Join Date
    Jan 2003
    Location
    England
    Posts
    3
    Unfortunatley, the user is already set up as db_datareader for all required databases (3 at the moment) and it has not made any difference.
    However, if I specifically go in and set select permissions for the user against the separate database tables then the user can select as expected.
    This is why I started looking for a global table set method.

    Neil.

  6. #6
    Join Date
    Sep 2002
    Posts
    12
    Neil,

    Like Jbane said All you need is the db_datareader. But here is a script that should do the job. Run it against each database.

    set nocount on
    declare @object varchar(40)
    declare mycursor scroll cursor
    for
    select name from sysobjects
    where type = 'u'
    order by name

    open mycursor
    fetch first from mycursor into @object
    while @@fetch_status <> -1
    begin
    if @@fetch_status <> -2
    begin
    exec('grant select on '+@object+' to [Q4W_Report]')
    end
    fetch next from mycursor into @object
    end
    close mycursor
    deallocate mycursor

    Pan

    set nocount off

Posting Permissions

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