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

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)
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_privilegesWHERE 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)
grant option grant_wgo
Properties

Article ID: 274993 - Last Review: 10/21/2013 01:59:51 - Revision: 2.1

  • Microsoft SQL Server 7.0 Standard Edition
  • kbnosurvey kbarchive kbbug kbfix KB274993
Feedback