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
>>>
>>>
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only