FIX: Users Added Through Windows NT Groups Not Able to View List of Tables

Article translations Article translations
Article ID: 221243 - View products that this article applies to.
This article was previously published under Q221243
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 53566 (SQLBUG_70)
Expand all | Collapse all

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.

Properties

Article ID: 221243 - Last Review: October 20, 2013 - Revision: 2.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB221243

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com