Query sysobjects for user table, not system table : sysobjects « System Tables Views « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. System Tables Views
3. sysobjects

3> EXEC sp_addrole 'db_dataupdaters'
4> GO
1> DECLARE tables_curs CURSOR FOR
2>     SELECT name FROM sysobjects
3>     WHERE type='U' 
4> OPEN tables_curs
5> DECLARE @tablename varchar(30), @output_msg varchar(80)
6> FETCH NEXT FROM tables_curs INTO @tablename
7> WHILE (@@FETCH_STATUS=0 )
8>     BEGIN
9>     EXEC ('GRANT UPDATE ON ' + @tablename
10>            + ' TO db_dataupdaters')
11>         IF (@@ERROR=0)
12>             SELECT @output_msg=
13>                 'UPDATE permission granted on table '
14>                 + @tablename
15>         ELSE
16>             SELECT @output_msg=
17>                 'Failed to grant UPDATE permission on table '
18>                 + @tablename + ' @@ERROR=' +
19>                 CONVERT(varchar, @@ERROR)
20>     PRINT @output_msg
21>     FETCH NEXT FROM tables_curs INTO @tablename
22>     END
23> CLOSE tables_curs
24> DEALLOCATE tables_curs
25>
26> GO
UPDATE permission granted on table pub_info
UPDATE permission granted on table authors_CS
UPDATE permission granted on table spt_fallback_db
UPDATE permission granted on table spt_fallback_dev
UPDATE permission granted on table spt_fallback_usg
UPDATE permission granted on table titleauthor
UPDATE permission granted on table department_pivot
UPDATE permission granted on table BankerBalances
UPDATE permission granted on table SalesMw
UPDATE permission granted on table BillingCopy
UPDATE permission granted on table spt_monitor
UPDATE permission granted on table spt_values
UPDATE permission granted on table MSreplication_options
UPDATE permission granted on table OldBillings
UPDATE permission granted on table Table1
UPDATE permission granted on table Table2
1>
2>