Tuesday, February 14, 2012

database owner is needed

hi,

on 1 of my servers (actually, the dev. server I have setup up here at home), I must included the database owner everytime I select something or make a db call.

for example

select T.foo from K inner join T on T.id = K.id

must actualy be written like:

select dbo.T.foo from dbo.K inner join dbo.T on dbo.T.id = dbo.K.id

or else it wont work

what did I do wrong for this database owner thing to be a "must" when writing queries on this server.

spec:
sql 2000 sp4You are not connecting as a dbo user.|||

That's wierd. What error are you getting? It should check the dbo owned objects after checking for an object owned by you.

|||

Amir, please update thread.

Thanks,

Derek

|||sorry folks,

I was tied up on another project for the week. I came back and tried to

trouble shoot instead of wasting you guy's time but at the end, i

failed.
basically, here is the situation:

the actual server that will end up hosting the project is setup fine so if i leave out the dbo.* or user.* and just type select * from table it works.

right now, i'm resorting to including the dbo in my queries... and evertime i upload to the server, i do a search in that folder and replace " dbo." with "" in all files.

let me explain how i made this database:

originally, it was on a dev server somewhere in US...
i made the db on my home server. Then created a user and gave it admin privilages over that db.
Then i did "All Tasks > Import" and imported the tables from that server, to this new home db. I had to go back and manually select the primary key for each table, as they got lost during the transfer.

now, when i look at the "Server Explorer" cluster of tables in this new database, i see that they all have (dbo) beside their names, meaning the owner of each table by default is dbo.

i think that should pretty much cover everything....
any clue as to why user "must" be specified?|||

What do you get on the server that requires dbo. when you execute:

select suser_sname(), user_name()

Also, for some object where you have to enter dbo. for the object, execute:

select *
from sysobjects
where name = '<name>'

select *
from information_schema.tables
where table_name = '<name>'

Perhaps this will shed some light? Also what is in @.@.version?

This might just be a stumper that requires a higher power :)

|||

> What do you get on the server that requires dbo. when you execute:

>select suser_sname(), user_name()

| __|_
| foouser | foouser

>select *
>from sysobjects
>where name = '<name>'

irrelevant

> select *
> from information_schema.tables
> where table_name = '<name>'

TABLE_SCHEMA is 'dbo' for all objects

conclusion:
objects where created as dbo when they were imported from the server on the hosting company to the local staging environment;

how can i prevent this when using SQL 2000 enterprise manager for import

(note: from my exp. with sql 2005, this problem wasn't encountered when going through the same steps -- or rather similar steps -- using native sql 2005 import/export functionality within the SQL 2005 studio)


side note:
i rather find a script based answer rather learning to use the GUI, because of my DB2, mysql background -- obviously i'm not gifted when using GUI tools



No comments:

Post a Comment