SQL Cursor grant update permission
SQL
Download (.zip)
-- Use a cursor and EXECUTE(@string) to update stats on every table. EXEC sp_addrole 'db_dataupdaters' GO DECLARE tables_curs CURSOR FOR SELECT name FROM sysobjects WHERE type='U' -- user table, not system table OPEN tables_curs DECLARE @tablename varchar(30), @output_msg varchar(80) FETCH NEXT FROM tables_curs INTO @tablename WHILE (@@FETCH_STATUS=0 ) BEGIN EXEC ('GRANT UPDATE ON ' + @tablename + ' TO db_dataupdaters') IF (@@ERROR=0) SELECT @output_msg= 'UPDATE permission granted on table ' + @tablename ELSE SELECT @output_msg= 'Failed to grant UPDATE permission on table ' + @tablename + ' @@ERROR=' + CONVERT(varchar, @@ERROR) PRINT @output_msg FETCH NEXT FROM tables_curs INTO @tablename END CLOSE tables_curs DEALLOCATE tables_curs
|