Microsoft KB Archive/173568

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

Article ID: 173568

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q173568



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.

Additional query words: object manage SEM

Keywords: kbbug kbfix kbusage KB173568

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.