PSIcapture Database Lookup: SQL DB lookup does not show table(s)

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.