FIX: Column Level Permissions May Be Displayed Incorrectly When GRANT_WITH_GRANT Option Is Used

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

SYMPTOMS

Applying the GRANT_WITH_GRANT option (also known as "GRANT_WGO" or "with grant option") at a column level can result in incorrect security permissions being displayed under some circumstances. This problem can be seen when running sp_helprotect, and when viewing permissions in Enterprise Manager. This is only a display problem however; actual server-side security checks are correctly applied to all users.

CAUSE

The bitmap value stored in the "Columns" column in the sysprotects pseudo-table can be built incorrectly under certain situations when the GRANT_WITH_GRANT option, DENY statement, and column level permissions are combined. Enterprise Manager and some of the system stored procedures read from the sysprotects pseudo-table.

WORKAROUND

  • Avoid using a combination of the GRANT_WITH_GRANT option, DENY statement, and column level permissions.
  • Use the information_schema built-in system view instead of sp_helprotect or Enterprise Manager to obtain column level permissions settings. For instance, to verify security permissions, you can run the following query:
    SELECT * FROM information_schema.column_privileges
    WHERE table_name = @TableName
    					

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

For more information, see the following SQL Server Books Online topics:
  • Grant (T-SQL)
  • Revoke (T-SQL)
  • Deny (T-SQL)

Properties

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

Give Feedback

 

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