Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Microsoft Query lists user objects and system views in the query wizard

Microsoft Query lists user objects and system views in the query wizard

Symptoms

When you use an ODBC data source (DSN) to access Microsoft SQL Server database from Microsoft Excel, you will notice that the Available Tables and Columns section in Query Wizard – Choose Columns page, by default, displays the following objects:

  • User tables and Views that are accessible for the user connecting to the data source.
  • System views and Catalog views available under the SYS schema of Microsoft SQL Server.
  • System views and Catalog views available under the INFORMATION_SCHEMA schema of Microsoft SQL Server.
You only observe this behavior when the backend SQL Server that the DSN is pointing to is Microsoft SQL Server 2005 or a later version. For Microsoft SQL Server 2000 and below, you will observe that the Available Tables and Columns lists only the user tables and views for which the user has permissions to use.


A sample screenshot of the wizard when connected to SQL Server 2005 or later versions is shown below:



↑ Back to the top

Cause

Microsoft Query, like any other ODBC application, uses sp_tables stored procedure to obtain a list of objects that are present in a specific database. As you can notice from the definition of sp_tables, this stored procedure returns a list of objects that can be queried in the current environment. The following is the query submitted to Microsoft SQL Server:

exec [DatabaseName]..sp_tables NULL,NULL,N'DatabaseName',N'''TABLE'',''VIEW'',''SYNONYM'''

The results of the query will be based on which version of SQL Server you are using:
  • In SQL Server 2000 and below, this call would return the list of only user objects.
  • In SQL Server 2005, this call will return the list of all accessible user objects, user views as well as the system views and catalog views (that belong to the SYS and INFORMATION_SCHEMA schema).

SQL Server 2005 introduced significant changes around the security model related to the following components:

As a result when you query for the list of all objects within a specific database, you will get the list of all system views and catalog views which are applicable system wide. Even though you get the list of all system views and catalog views, the information returned by each one of the system views and catalog views is controlled by the individual permissions that are needed to view that information.

↑ Back to the top

Resolution

If you do not want to list the system views and catalog views in the Query Wizard, you can use one of the following methods:

Method 1:Instead of using the Microsoft Excel option [Data -> Get External Data -> From Other Sources -> From Microsoft Query] use one of the following methods to import data into the worksheet:
  1. [Data -> Get External Data -> From Other Sources -> From Data Connection Wizard]
  2.  [Data -> Get External Data -> From Other Sources -> From SQL Server]

The Data Connection Wizard and the SQL Server option uses OLEDB provider to connect to SQL Server instead of ODBC DSN. Apart from this, these wizards automatically filter out the system views and catalog views and list only the user tables and views, as show in the screenshot below:





Method 2: Configure Microsoft Query to show only user tables. You can use the OPTIONS available in the Query Wizard to customize the type of objects that are listed. If you uncheck the VIEWS option, then the “Available tables and columns” section will list only the user tables. This is a one-time change and the selection is saved for this user until the user changes it back again. You do not need to make this change every time you launch Microsoft Excel.





Method 3: Configure Microsoft Query to show only user tables and views that belong to a specific schema (for example: dbo). You can use the OPTIONS available in the Query Wizard to customize the owner of objects that are listed. If you select "dbo" as owner, then the Available tables and columns section will list only the user tables and views that are created under the "dbo" schema. 




Method 4: Use Windows registry to control the objects that are displayed by the Query wizard.
Note: The registry key and the associated sub keys will only be created on your system after you run through the Query Wizard at least once on that system.

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
322756 How to back up and restore the registry in Windows
  1. Open the registry editor using Regedit and navigate to the following registry key:
    HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Query\Table Options
  2. Modify the Tables value to 1.
The registry key may have values similar to the following after the above change:
"Views"=dword:00000000
"Tables"=dword:00000001
"Synonyms"=dword:00000001
"SysTables"=dword:00000000
"AlphabeticalItemOrder"=dword:00000001

Note: The number 14.0 in the path shown above will change depending upon the version of Microsoft Excel you have on your system.

Once you setup the Table options, the Query Wizard will list only the user tables.



↑ Back to the top

More Information

The stored procedure sp_tables internally gets the list of objects for a database from the catalog view sys.all_objects. The output of the catalog view is a union of all objects that are present within the specific user database as well as the system wide views that are present in the Resource database.

Even though the Query Wizard lists all system wide views and catalog views, a user cannot use them to arbitrarily get any information from the server. The user needs specific permissions for each view to be able to get the information provided by these views. As shown below the user gets an error message when he tries to access these objects within the query wizard without having the required permissions on the backend database:








↑ Back to the top

Keywords: kb

↑ Back to the top

Article Info
Article ID : 2513216
Revision : 1
Created on : 1/7/2017
Published on : 4/28/2011
Exists online : False
Views : 107