Granting permissions to multiple objects

,

I came across a situation where I needed to fix a vendors permissions issue.  It required changing permissions on 338 views and tables, and leaving only 5 intact.  Below is the Script I used to perform this task.

Use DB1
--Set Table variable
Declare @tbl as varchar(100)

--Declare Cursor
Declare tbl_cursor cursor for
--Get User Tables and user views
SELECT     name
FROM         sysobjects
WHERE     (xtype IN ('v', 'u')) AND (category <> 2) and name not in('Table1',
'Table2', 'Table3', 'Table4', 'Table5')

--Open Cursor
open tbl_cursor

--Set cursor loop
WHILE @@FETCH_STATUS = 0
BEGIN
--Get next record assign to variable
Fetch next from tbl_cursor
into @tbl

--Build and execute grant statement for each table
exec('GRANT INSERT, UPDATE,  Select
ON ' + @tbl + ' TO TestGroup')

--Close loop
end
--Close tbl_cursor
close tbl_cursor
deallocate tbl_cursor

Rate

Share

Share

Rate