Environment: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Two logins on the system, sa and Fred. Fred is a member of System Administrators.
Fred creates a new database, let’s call it Sales, done via a simple script in Query Analyzer (QA).
In Enterprise Manager (EM) when I right click on this database, select properties, I can see that Fred is the database owner.
Fred now creates a table with himself as the owner, something like the script below, this is done via QA with Fred as the user / login.
CREATE TABLE [Fred].[Customers] (
This is the only table in the database.
With Fred logged into QA, I’m expecting this select statement (A) to work.
SELECT * FROM Customers
But it errors out, with the following:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Customers'.
The following select (B), does work.
SELECT * FROM Fred.CustomersI’ve got tons of Visual Basic 6 code that uses the select without the qualified database owner. This code was written a long time ago, but now I’ve got to get back into it. For some reason, the non-qualified select statement used to work and does work at many sites still running this code. But in my development environment I get the “Invalid object name” error.
I’d prefer to not change the VB6 code, seems like this should work, or at least used to work.
So, can I setup SQL so that when Fred logs in he can default to use the object he owns and successfully run statement A from above?