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

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 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.