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]?

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

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.

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

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.

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