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

View products that this article applies to.

This article was previously published under Q274428

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.

Back to the top

WORKAROUND

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

Back to the top

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 (http://support.microsoft.com/kb/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.

Back to the top

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.

Back to the top