Microsoft KB Archive/173568

From BetaArchive Wiki
Knowledge Base


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