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