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


>I copied this from Books Online, search for fetch and I think fetch next will achieve what you want.
>
>DECLARE authors_cursor CURSOR FOR
>SELECT au_lname FROM authors
>WHERE au_lname LIKE "B%"
>ORDER BY au_lname
>
>OPEN authors_cursor
>
>-- Perform the first fetch.
>FETCH NEXT FROM authors_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.
> FETCH NEXT FROM authors_cursor
>END
>
>CLOSE authors_cursor
>DEALLOCATE authors_cursor
>GO
>
>
>>You are correct. However, what I really need is to grant the read permission for all tables except a number of confidential tables. My initial plan was to write a "Grant_Persmission" stored procedure which gets a cursor listing of user tables excluding those confidential tables. Then loop through them and grant the permission accordingly. Unfortunately, I stumble on the looping through the cursor using TSQL.
>>
>>I am sorry for not specifying my problem clearer in the earlier mail. Any idea how I can solve my problem?
>>
>>Thanks
>>
>>>I thought the db_datareader role covers this? Is this what you want?
>>>
>>>Explain a little more if ive missed the point.
>>>
>>>Jon
>>>
>>>