Microsoft KB Archive/274993

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

Article ID: 274993

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q274993



BUG #: 58184 (SQLBUG_70)



SYMPTOMS
Applying the GRANT_WITH_GRANT option (also known as &quot;GRANT_WGO&quot; or &quot;with grant option&quot;) 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 &quot;Columns&quot; 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)

Additional query words: grant option grant_wgo

Keywords: kbbug kbfix KB274993

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.