Microsoft KB Archive/173568
Article ID: 173568
Article Last Modified on 3/14/2006
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q173568
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:
You cannot resolve the problem by closing and reopening SQL Enterprise Manager or choosing Refresh.
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
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