the owner in msssql 7

Question

the owner in msssql 7

zoltix

SSC Eights!

Points: 813
More actions
December 9, 2002 at 9:56 am

#58550

Hi,
I have a problem with the owner in msssql 7. I try to recompile a table but mssql said “Invalid object name 'CatTitles'.”. CatTitles exists but with one other owner also I don’t know how to recompile these objects () from one other user.
It don’t works with setuser ‘username’ since sp3 (our server is in sp4)or I am omitted a bad configuration (which?)
Could you help me………..
Thanks

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

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

Not exactly sure what you are asking. But if you want change the owner of the table use the "sp_changeobjectowner" SP. Look in BOL for complete syntax.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

You cannot "recompile" a table. You drop it and recreate it. You can alter it.

Can you post the code you are trying to run?

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

zoltix SSC Eights! Points: 813 More actions · Answer 3

example:

Work sql 7 sp 2 but not sp4.

Setuser ‘bruprod’

Go

Sp_recompine ‘CatTitles’

Go

zoltix SSC Eights! Points: 813 More actions · Answer 4

in our sql 7 sp2

connect with:"bruprod"

select *

from catTitles

ok

in our sql 7 sp4(other server)

connect with: "bruprod"

select *

from catTitles

not work "Invalid object"

but

select *

from bruprod.catTitles

ok ?

I do not understand what ?

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 5

have you tried:

sp_recompile <database>.bruprod.catTitles

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 6

Sorry that really should be:

sp_recompile '<database>.bruprod.catTitles'

where you replace <database> with the name of the database where catTitles is an object.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

zoltix SSC Eights! Points: 813 More actions · Answer 7

How to change the default owner? For example, I would change for user “tata” with owner bruprod by default. In fact, when the user makes a query, he doesn’t need to precise the owner. Ex “select * from CatTile” should be worked instead of “select * from owner.CatTile“

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 8

I personally always have all objects owned by "DBO" then any user in the database can access that object if they have the appropriate permissions.

Are you trying to allow multiple users access to objects owned by "bruprod", without specify the owner, via the SETUSER command? If so you might consider change the owner from bruprod to DBO.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP