FIX: Managing Permissions Fails with Error 21770 for User- Owned Tables

ID: Q173568

The information in this article applies to:

Microsoft SQL Server version 6.5

SYMPTOMS

Managing permissions for a group by means of the By User tab (found in SQL Enterprise Manager by clicking Permissions on the Object menu) will fail for user-owned tables with the following message:

Error 21770: The name '*%s' was not found in the Tables collection.

You cannot resolve the problem by closing and reopening SQL Enterprise Manager or choosing Refresh.

WORKAROUND

To work around this problem, do either of the following:

Assign permission by means of the By Object tab.

Use the GRANT statement to assign rights to the table.

You can execute the following cursor in the database to assign permissions to all tables in the database. You can edit the cursor to narrow down the rights granted or revoked, or the tables affected.


set nocount on

declare @tbl_name varchar(30)
declare @owner varchar(30)
declare @str varchar(255)

declare tbl_cur cursor for
select o.name, u.name from sysobjects o, sysusers u
where o.type = 'U'
and o.id > 18
and o.uid = u.uid


open tbl_cur
fetch tbl_cur into @tbl_name, @owner

while @@fetch_status <> - 1
begin
select @str = 'Grant select, insert, update, delete, references on '
+ @owner + '.' + @tbl_name + ' to public'
EXEC (@str)
select @str
fetch tbl_cur into @tbl_name, @owner
end

close tbl_cur
deallocate tbl_cur

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

Additional query words: object manage SEM

Keywords : kbusage SSrvGen kbbug6.50 kbfix6.50.SP5
Version : winnt:6.5
Platform : winnt
Issue type : kbbug