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

The information in this article applies to:

Microsoft SQL Server 7.0

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)

Modification Type:	Major	Last Reviewed:	3/14/2006
Keywords:	kbbug kbfix KB274993