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 "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)
Additional query words: grant option grant_wgo
Keywords: kbbug kbfix KB274993
