- 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.
A sample screenshot of the wizard when connected to SQL Server 2005 or later versions is shown below:
exec [DatabaseName]..sp_tables NULL,NULL,N'DatabaseName',N'''TABLE'',''VIEW'',''SYNONYM'''
- 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.
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:
- [Data -> Get External Data -> From Other Sources -> From Data Connection Wizard]
- [Data -> Get External Data -> From Other Sources -> From SQL Server]
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:
- Open the registry editor using Regedit and navigate to the following registry key:
- Modify the Tables value to 1.
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.
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:
Raksta ID: 2513216. Pēdējo reizi pārskatīts: 2011. gada 28. apr.. Pārskatījums: 1