How to remove no owner table?

Question

Post reply

How to remove no owner table?

joemai

Hall of Fame

Points: 3496
More actions
July 9, 2005 at 9:24 am

#109808

The tables had no owner because the databases were restored from backup of different server. I tried to use sp_changedbowner to fix the problem, but I still couldn't delete those tables. Please advise.
Minh Vu

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

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

If you do something like...(see below)...what do you get ?!

select su.name as username, so.name as tablename

from

sysobjects so

inner join

sysusers su

on

so.uid = su.uid

where

so.xtype = 'U' and

so.name in ('table1', 'table2', 'table3', 'table4')....

**ASCII stupid question, get a stupid ANSI !!!**

ND-215452 SSCrazy Points: 2037 More actions · Answer 2

not sure what you mean by no owner?

As far as I know owner can not be null.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I've had this before, though I don't know what caused it. Looking at the table list in Ent Manager the table had no owner listed. When I checked sysobjects, I found that the uid didn't match anything in sysusers for that database.

I ended up enabling direct updates and updating sysobjects (I know, really not recomended)

btw, this post should have been in SQL Administration or SQL General, not in the Question of the Day forum.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

joemai Hall of Fame Points: 3496 More actions · Answer 4

joemai

Hall of Fame

Points: 3496

July 11, 2005 at 9:50 am

#572978

Hi Sushila,

I got

username table_name

dbo

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

That's strange - if the tables don't exist in sysobjects then you should get no rows back...

how about if you just query sysobjects with:

select uid, name from sysobjects

where name in ('table1', 'table2', 'table3'...)

you may have to end up directly updating sysobjects like GilaMonster...:-(

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 6

the first thing I would try is to make sure that all the users in the db map to the corresponding login on the new server.

have a look at : sp_change_users_login

ex: EXEC sp_change_users_login 'Update_One', 'DBUSER', 'SERVERLOGIN'

* Noel

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

I also can't tell you what caused it, but I ran into the same issue. I ran change owner proc against it to set the owner to a user that was in the system and then was able to delete it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 8

There is a workaround.

Please, check on the old server what was the owner, his/her/it name and sid by:

use myolddatabase

select name, sid from master.dbo.syslogins where sid =

(select sid from sysusers where name = 'tableownername')

After that use sp_addlogin on the new server with the full syntax that inludes specifying SID as well:

execute sp_addlogin @loginame = myname, @passwd = whatever, @defdb='whatever is on the old server',@sid = SID from the query

This way you will have same login with the same SID as on the old server so you may be able to see owners.

Yelena

Regards,Yelena Varsha