Try this.

/* declare the variable for the current table and schema */
declare @CurrTable varchar(40), @CurrSchema varchar(40)

DECLARE tables_cursor CURSOR FOR
SELECT TABLE_NAME, table_schema
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tables_cursor

-- Perform the first fetch.
FETCH NEXT FROM tables_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
-- EXEC sp_addrolemember 'db_datareader','Jon'
FETCH NEXT FROM tables_cursor into @CurrTable, @CurrSchema

/* grant permission, but 'grant' does not like @CurrTable */
/* grant select on @CurrTable to ReadOnlyUser
EXEC ('GRANT SELECT ON [' + @CurrTable + '] TO ReadOnlyUser')
END

CLOSE tables_cursor
DEALLOCATE tables_cursor

Jon

>It looks good. But I still have problem trying to grant permission on the tables. I added 2 lines to your code which now looks like:
>

>/* declare the variable for the current table and schema */
>declare @CurrTable varchar(40), @CurrSchema varchar(40)
>
>DECLARE tables_cursor CURSOR FOR
>SELECT TABLE_NAME, table_schema
>FROM INFORMATION_SCHEMA.TABLES
>WHERE TABLE_TYPE = 'BASE TABLE'
>
>OPEN tables_cursor
>
>-- Perform the first fetch.
>FETCH NEXT FROM tables_cursor
>
>-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
>WHILE @@FETCH_STATUS = 0
>BEGIN
>  -- This is executed as long as the previous fetch succeeds.
>  -- EXEC sp_addrolemember 'db_datareader','Jon'
>  FETCH NEXT FROM tables_cursor into @CurrTable, @CurrSchema
>
>  /* grant permission, but 'grant' does not like @CurrTable */
>  grant select on @CurrTable to ReadOnlyUser
>
>END
>
>CLOSE tables_cursor
>DEALLOCATE tables_cursor
>

>
>The parser fails the line

>  grant select on @CurrTable to ReadOnlyUser
>

>because it is expecting an known object/table, not a variable. How do I overcome this?
>
>Thanks.
>
>
>>Have a look at this it may point you in the right direction.
>>
>>DECLARE tables_cursor CURSOR FOR
>>SELECT TABLE_NAME, table_schema
>> FROM INFORMATION_SCHEMA.TABLES
>> WHERE TABLE_TYPE = 'BASE TABLE'
>>
>>OPEN tables_cursor
>>
>>-- Perform the first fetch.
>>FETCH NEXT FROM tables_cursor
>>
>>-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
>>WHILE @@FETCH_STATUS = 0
>>BEGIN
>> -- This is executed as long as the previous fetch succeeds.
>> EXEC sp_addrolemember 'db_datareader','Jon'
>> FETCH NEXT FROM tables_cursor
>>END
>>
>>CLOSE tables_cursor
>>DEALLOCATE tables_cursor
>>GO
>>
>>
>>HTH
>>
>>Jon
>>
>>