(dbo)?

[jgoodman00]

If I connect to our SQL db as myself, I get object names such as "tblLineGen".

However, if I connect as anybody else they are suffixed with "(dbo)", so they become "tblLineGen (dbo)".

This is causing havoc with my procedures because the object names are changing.

Any suggestions for a solution?

James Goodman

http://www.james-goodman.co.uk

 

[jgoodman00]

The answer was very simple.

Fully qualify object names from "tblLineGen" to "dbo.tblLineGen".

Since then no problems . James Goodman

http://www.james-goodman.co.uk

 

[janettx]

How do you fully qualify? I'm having the same problem. Thanks,
Janet

 

[osjohnm]

Hi Janet

To fully qualify just select like this,

select * from dbo.employee.

select * from john.users

just add the owner infront of the table name.

you can do the same in inserts and updates as well.

you can also join between databases on the same server by specifying(fully qualifying) the database as well.

select * from ipmlive.dbo.cost_centre
where cc_code in(select cc_code from ips2live.dbo.order_group)

Does that make sense?

John

 

[jgoodman00]

Yup, thats exactly what I did. Now waiting for forgotten lines of SQL to error due to my not fully qualifying them... James Goodman

http://www.james-goodman.co.uk

 

[janettx]

Not really. When I use export to export one database to another database on another server, I'm finding that some of my objects transfer as dbo and some transer as me. I'm very new to sql and I still don't have my permissions right or something because I can not get the stored procs to go over. But I was wondering what I have to do to get the objects over as either all me or all dbo. Perferably dbo?

 

[osjohnm]

Hi Janet

Ideally all database objects should be owned by 'dbo' or atleast the stored procs, triggers and functions. Anyone can own an object but the more owners there are the harder it becomes when you have to fully qualify tables in queries.

select * from dbo.table1, john.table2, kim.table3 etc

I wouldn't suggest trying to dts procedures from one database to another database since it tends to cause issues.
If a sp references a certain table or field in the soruce db and that table or field isn't in the destination table it will fail and usually that causes the dts to fail.
If you are copying all the objects in the database then it should be fine.

rather right click on the database, all tasks, generate sql script, select stored procs, create and drop options and you can also script the permissions on the procs but make sure that all the users exist in the other db or server.
Then execute that script against the other db.

here is short def. about database object owners from BOL:

Database Object Owner
A user who creates a database object (a table, index, view, trigger, function, or stored procedure) is called a database object owner. Permission to create database objects must be given by the database owner or system administrator. However, after these permissions are granted, a database object owner can create an object and grant other users permission to use that object.

Database object owners have no special login IDs or passwords. The creator of a database object is granted all permissions implicitly but must give explicit permissions to other users before they can access the object.

Hope that helps Janet.

John

 

[SQLSister]

Yeah, we always require everyone to specifically create all objects with dbo as owner too. IF I see an object owned by someone else, I immediately change it to dbo. Have never had a problem moving the objects and the ownership changing, but we do only allow system admins to move objects to other databases, so I suppose there is a possible permissions problem if you aren't already an owner in the database or a sysadmin.

Stored procedures can't be moved with simple import or export that you would use to send data over. You can script them, then run the script on the new server.