Restoring a database to a different server and dealing with table/db object ownerships

[erin]

When I run:
select * from testtable

I get this error message:

Invalid object name 'testtable'.

However, if I run:

select * from testuser.testtable

It works. I'm logged in as testuser, and testuser is the database
object owner and tableowner. This was a restored database from a
different server in SQL Server 2000. The testuser login was in the
previous database. I created the same login in this new test database
and made it database owner.

I've also tried running: sp_change_users_login 'Update_One',
'testuser', 'testuser'

It completed successfully but I still get:

select * from testtable

Invalid object name 'testtable'.

I've also tried creating a different user and making it database owner
and when I change table object owner to this new user, I still get the
same problem. I need to specify:

select * from newtestuser.testtable

Even though I'm logged in as newtestuser.

I also then tried changing table owner to dbo. This seems to work
logged in as either user, but I want to have the tables owned by the
user not dbo. How can I resolve this?

Any help would be appreciated.

Erin

[Erland Sommarskog]

[posted and mailed, please reply in news]

erin (er********@yahoo.com) writes:

When I run:
select * from testtable

I get this error message:

Invalid object name 'testtable'.

However, if I run:

select * from testuser.testtable

It works. I'm logged in as testuser, and testuser is the database
object owner and tableowner. This was a restored database from a
different server in SQL Server 2000. The testuser login was in the
previous database. I created the same login in this new test database
and made it database owner.

A little of terminology confusion here.

login - this is an entity that exists on the server level.
user - this is an entity that exists on database level. A database user
may map to a login, but it does have to be the case.

When you restore a database from another server, you can get unexpected
mappings between users and logins. You can use sp_helpuser to see
what the current mappings are.

If you make a login an owner of a database, that login maps to the
user dbo in the database. So if you are logged in as testuser, and
testuser owns the database, and you try to access a table owned by
the *database user* testuser, you need to access the table with
a two-part name. This is because the database user maps to another
login - or no login at all.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp