Article ID: 221243
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q221243
BUG #: 53566 (SQLBUG_70)
SYMPTOMS
When permissions are granted to a Windows NT group, the users of the group will not be able to see a list of the tables for which they were given permissions. However, certain conditions must be met for this behavior to occur. The conditions that must be present for this problem to occur are listed in the MORE INFORMATION section of this article.
Windows NT groups can be granted access to SQL Server, which inherently gives access to all of the Windows NT users who are members of the group. Object permissions can then be applied to the group. When a user from the group logs in to SQL Server using Windows NT authentication, that user will not be able to enumerate the list of tables for which he or she has permissions. This problem can occur when the user issues the sp_tables stored procedure or selects from the INFORMATION_SCHEMA views.
The permissions granted to the user function properly. That is, SELECT, UPDATE, DELETE, and so on work correctly.
This problem can also occur with programs using ODBC drivers that call sp_tables. This includes Sqlsrv32.dll version 3.70.06.23 and later.
CAUSE
The cause of this problem has been traced to the permissions function, which is called by INFORMATION_SCHEMA and sp_tables.
WORKAROUND
Adding SQL Server logins for each user in the group is not necessary. Enumerate the users of the group using "xp_logininfo 'groupname', members" or by using the Windows NT command line NET LOCALGROUP groupname. Then grant access to the necessary tables for the Windows NT users.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
232570INF: How to Obtain Service Pack 1 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
MORE INFORMATION
The conditions required for this behavior to occur are:
- The user has not been granted access explicitly.
- The user has not been granted explicit permissions to the object.
- The user is connected to SQL Server using Windows NT authentication.
Keywords: kbbug kbfix KB221243
