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

• Microsoft SQL Server version 7.0

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 version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

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.

Additional query words:

Keywords          : kbbug7.00 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: June 8, 1999