Table created under 'dbo' for any login

Question

Post reply

Table created under 'dbo' for any login

rshivagami

Grasshopper

Points: 19
More actions
February 25, 2003 at 9:42 am

#58916

Hi all,
I have a DB called 'OSS' on SQL 2000. I created a user 'w_provision' with 'db_owner' privileges on this db. Now, when I log in as 'w_provision' into Query Analyzer to create a table, the table gets created under 'dbo'. The same case when using DTS too. Ideally it should get created as the user whoever has logged in ....Does anyone know where I am going wrong ?
Thanks,
Shiva.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

Try creating the table like CREATE TABLE oss.w_provision.Tablename

and remember to fully reference the table when executing SELECT statements

rshivagami Grasshopper Points: 19 More actions · Answer 2

Thanks Racosta. That did work, but I need to be able to access the table as

OSS.table_name. All the front end queries are already formatted that way.

Shiva.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Accessing OSS.table_name means you have already created a table that is owned by owner 'OSS'. You must have a user named 'OSS' in database OSS. If you don't have it, create a login 'OSS' and grant it to access OSS database.

Or change all objects's owner to 'OSS' with sp_changeobjectowner.

Edited by - Allen_Cui on 02/25/2003 12:19:09 PM

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 4

If you want to access the table like this:

SELECT * FROM OSS..TableName Then SQL server will first look for the user that executed the command and then will look for the table

OSS.dbo.TableName.

IF the user w_provision executes the select I said before, then it shouldn't be any problems finding the table, but if another user execute the query, to find the table you must execute :

SELECT * FROM OSS.w_provision.TableName.

If you cant avoid changing your queries, then you must execute the commands with that user.

rshivagami Grasshopper Points: 19 More actions · Answer 5

Thanks for all your replies. Both your strategies - SP_CHANGEOBJECTOWNER and execution with login as 'w_provision' will serve the purpose, but I still am wondering why is the user who created the object not the owner, but 'sa' is ?

Shiva.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 6

'sa' can create tables under any user's name provided the user's name is exist in the database.

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 7

Only when the sa executes CREATE TABLE, SQL uses dbo to specify the table owner. Any non sa DB owner, to get his table like dbo.name should specify it explicity in the CREATE TABLE statement.