When & how to use OWNER name in a query

[tabbytab]

Hi Guys

As you will see from my question I know virtually zero about SQL.

I have a hosted MS SQL DB. When I run queries against it using ASP I do not use the Owner name infront of the table name at all.

When I download backup of DB and restore to local machine I must use owner in reference to tables eg
dbo1234567.tblabcdef.

How can I just use the table name to refer to a table object

Thanks in advance
TabbyTab

 

[SQLBill]

Are you an administrator on the database? Probably not. To use just the tablename, you have to be an adminstrator AND there can be only one table with that name.

BEST PRACTICE is to always use the database.owner.table naming convention.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

This is pretty standard with hosting companies.

The object owner on the hosting server will also be the username of your SQL Account. When you created the objects you didn't specify an owner, so SQL defaulted to the username of the user who created the objects.

You have a couple of options.

1. Use the owner name of the table. This way your local system matches the production system, and any changes you know will work.

2. You can change the objects owner from dbo123456 to dbo with the sp_changeobjectowner procedure. You can find more info on this procedure in Books OnLine.

I would recommend number 1. However if you decide to use number 2 I would recommend also changing the object owner on the hosting server from dbo123456 to dbo so that it matches your local system.

If you do make the change remenber that as you make new objects via the hosting companies web interface you will need to change the owner to dbo.

I would also recommend putting the owners name in front either way to prevent confusion.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com