SQL Server: How to View Tables when Setting up a SQL Server Database Lookup

Problem

A user is trying to setup a SQL Server Lookup, but when they enter the SQL Server information and username information the tables are not listed for selection.

Solution

SQL Server 2008 requires that the user has VIEW ANY DEFINITION granted on the Master database. Without it, the user will not see any tables in the dropdown when configuring a lookup using SQL Server.

USE master

GO

GRANT VIEW ANY DEFINITION TO User1

-------------------------

OR YOU CAN USE:

-------------------------

USE master

GO

GRANT VIEW ANY DEFINITION TO public

-------------------------

The SP_DATABASES stored procedure is used to get the list/catalog, and you have to have permission to access it.


If you are still experiencing difficulties, please contact our Support team by: