FIX: Revoking Update Permissions on a Table Also Revokes Column-Level Select Permissions

Article translations Article translations
Article ID: 274428 - View products that this article applies to.
This article was previously published under Q274428
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 57453(SQLBUG_70)
Expand all | Collapse all

SYMPTOMS

Revoking an update permission on a table that was previously granted also removes any column-level select permissions that have been granted.

WORKAROUND

Re-execute a script to grant the column-level selects after revoking the update table.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.

MORE INFORMATION

The following script reproduces the problem:
create table doo(goo int,boo int,soo int)
grant select on doo(goo,boo) to public
grant update on doo to public
exec sp_helprotect 'doo'
revoke update on doo from public
exec sp_helprotect 'doo'
drop table doo
				
The following are the results:
Owner  Object Grantee      Grantor ProtectType Action Column             
-------------------------------------------------------------
dbo    doo    public       dbo     Grant       Select goo
dbo    doo    public       dbo     Grant       Select boo
dbo    doo    public       dbo     Grant       Update (All+New)

(3 row(s) affected)
				
Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 344
There are no matching rows on which to report.

Properties

Article ID: 274428 - Last Review: October 21, 2013 - Revision: 2.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix kbsqlserv700fix KB274428

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com