Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

SQL 2000, Object Owner Question

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] (

     …

)

GO
 

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?
0
g_johnson
Asked:
g_johnson
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for system admins and db_owners, the object prefix defaults to dbo
and from what I know, you cannot change that behavior, except removing Fred as db owner ....
0
 
g_johnsonAuthor Commented:
Thanks, that did the trick.  We had assumed that "Fred" was always a sys admin, but we were wrong.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now