FIX: Permissions Denied on Certain Columns After Revoking One

• Microsoft SQL Server, version 4.21a

SYMPTOMS

If permissions are granted for full UPDATE and/or SELECT privileges to a group, but permissions are revoked for a single column for a user of that group, other columns in the table will be denied to that user.

When permissions for one column are revoked for the user, only the eighth column or greater (in the order they are created in the table definition) will be denied to the user. This behavior is independent on which column was used to revoke permissions.

Tables with eight columns or less do not exhibit the behavior. If permissions are granted and revoked at the group or user level, not mixed, the problem does not occur.

WORKAROUND

1. Grant or revoke permissions to the entire group or to individual users instead of using a mixed approach.

2. Create views to restrict access to columns instead of granting or revoking permissions at the column level.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.21a. This problem was corrected in SQL Server version 6.0. For more information, contact your primary support provider.