You administer a SQL Server 2000 computer that hosts a database named FinanceData used by a client/server application. You created a SQL Server login named Andrew and a corresponding database user named Andrew in FinanceData with full permissions. However, when Andrew runs the application, he receives an "Invalid object name" error and cannot access the database objects. Which Transact-SQL statement should you execute so that Andrew can use the application properly?

Introduction / Context:
In SQL Server 2000, each SQL login has a default database. When a client application connects without specifying a database, the connection is placed in that default database, and any unqualified table references are resolved there. If the default database is not the one the application expects, users may see "Invalid object name" errors even when they have appropriate permissions in the correct database. This question tests your understanding of how to fix such issues by setting the default database for a login.

Given Data / Assumptions:

• There is a SQL Server login named Andrew.
• A database user named Andrew exists in the FinanceData database.
• The user Andrew has been granted full permissions in FinanceData.
• When Andrew runs the client/server application, he receives "Invalid object name" errors.
• The application likely connects without explicitly specifying the database name, relying on the login's default database.

Concept / Approach:
If Andrew's default database is not set to FinanceData (for example, if it is set to master), then when he logs in, any queries that refer to tables without database qualifiers will be run in the wrong database. Even though the user has permissions in FinanceData, the application is not using that database. The system stored procedure sp_defaultdb allows you to change the default database for an existing login. Setting FinanceData as the default ensures that Andrew's sessions start in the correct database and that the application can access the objects it expects.

Step-by-Step Solution:
1. Confirm that the login Andrew exists and that a corresponding user Andrew exists in FinanceData with appropriate permissions. 2. Determine the current default database for Andrew; it might be master or another database. 3. Execute EXEC sp_defaultdb 'Andrew', 'FinanceData' to change the default database for the Andrew login. 4. Ask Andrew to reconnect or restart the client application so that new connections start in FinanceData. 5. Verify that the "Invalid object name" errors no longer occur when Andrew uses the application.

Verification / Alternative check:
After running sp_defaultdb, you can check the login properties in Enterprise Manager or by querying system tables to confirm the default database. Then, using Andrew's credentials, connect to SQL Server (for example, using Query Analyzer) and run SELECT DB_NAME() to confirm that the session starts in FinanceData. Queries that rely on unqualified table names should now resolve to objects in FinanceData instead of master.

Why Other Options Are Wrong:
Option B, GRANT ALL ON FinanceData TO Andrew, attempts to grant permissions on the database object itself and is not valid syntax for granting database-wide rights; moreover, the user already has full permissions. Option C, EXEC sp_addlogin 'Andrew', would try to create a new login, which is unnecessary and would fail if the login already exists. Option D, EXEC sp_grantdbaccess 'FinanceData', 'Andrew', is for adding a user to a database, which has already been done according to the scenario; running it again does not solve the default database problem.

Common Pitfalls:
A common mistake is assuming that permissions alone determine whether a user can access objects, when in fact the default database and the database targeted by the connection also matter. Developers may also hard-code unqualified object names in their applications, which increases the chance of connecting to the wrong database. It is good practice to either set the correct default database or explicitly specify the database in connection strings and object references.

Final Answer:
You should run EXEC sp_defaultdb 'Andrew', 'FinanceData' so that Andrew's default database is FinanceData and the application can access its objects without error.