No access at sql 2000 DB after restore on an other sql server

HI,
i've some trouble with a sql server 2000 db.
the db uses sql server auth
i make a backup from the DB and take the backup to an other sqlserver, i had
restored the database at ther new server.
i've create ^the db owner user on the new server and executed
sp_change_users_login 'Update_One', 'username', 'loginname'
now my asp .net application works fine. but i would like to modify some
tables from the DB if, i start query analyzer and login as the db owner and
start a query
select *
rom issues
at the restored db i recieved an error
"Server: Nachr.-Nr. 208, Schweregrad 16, Status 1, Zeile 1
Ungültiger Objektname 'issues'." means the Object issues is invalid, but th
e
table exists. the same query on the original db Server is ok i recieved the
right result
whats wrong?
Many Thanks for your helpflabs (flabs@.discussions.microsoft.com) writes:
> i've some trouble with a sql server 2000 db.
> the db uses sql server auth
> i make a backup from the DB and take the backup to an other sqlserver, i
> had restored the database at ther new server.
> i've create ^the db owner user on the new server and executed
> sp_change_users_login 'Update_One', 'username', 'loginname'
> now my asp .net application works fine. but i would like to modify some
> tables from the DB if, i start query analyzer and login as the db owner
> and start a query
> select *
> rom issues
> at the restored db i recieved an error
> "Server: Nachr.-Nr. 208, Schweregrad 16, Status 1, Zeile 1
> Ungltiger Objektname 'issues'." means the Object issues is invalid, but
> the table exists. the same query on the original db Server is ok i
> recieved the right result
Apparently your default schema on the server is not the schema where the
issues table is, nor is the table in the default schema of dbo. Note that
on SQL 2000 the default schema for a user is always the username.
It's not clear to me whether you did an sp_changedbowner of the database,
but you should probably have done that.
Run this query:
SELECT user_name(uid), *
FROM sysobjects
WHERE name= 'issues'
This should give you the owner/schema for the issues table.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hej
no, i didn't run sp_changedbowner. why must i run the sp ?
the result of SELECT user_name(uid), *
geminiuser issues 341576255 U 5 26 1610620982 64 0 0 2006-08-11
16:02:18.570 0 64 0 U 1 8291 0 2006-08-11 16:02:18.570 0 0 0 0 0 18433 0
i find out, if i run select *
from geminiuser.issues i recieved the right result
run i only issues without geniniuser. before issue i recieved an error.
what happens if i run sp_changeDBowner, sure i think it change the owner of
the db? but some else?
must i change all tables, add prefix geminiuser. in my sql script, i don't
like this.
"Erland Sommarskog" wrote:

> flabs (flabs@.discussions.microsoft.com) writes:
> Apparently your default schema on the server is not the schema where the
> issues table is, nor is the table in the default schema of dbo. Note that
> on SQL 2000 the default schema for a user is always the username.
> It's not clear to me whether you did an sp_changedbowner of the database,
> but you should probably have done that.
> Run this query:
> SELECT user_name(uid), *
> FROM sysobjects
> WHERE name= 'issues'
> This should give you the owner/schema for the issues table.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||flabs (flabs@.discussions.microsoft.com) writes:
> no, i didn't run sp_changedbowner. why must i run the sp ?
> the result of SELECT user_name(uid), *
> geminiuser issues 341576255 U 5 26 1610620982
> 64 0 0 2006-08-11
> 16:02:18.570 0 64 0 U 1 8291 0 2006-08-11
> 16:02:18.570 0 0 0 0 0 18433 0
> i find out, if i run select *
> from geminiuser.issues i recieved the right result
> run i only issues without geniniuser. before issue i recieved an error.
> what happens if i run sp_changeDBowner, sure i think it change the owner
> of the db? but some else?
> must i change all tables, add prefix geminiuser. in my sql script, i don't
> like this.
The simplest is if you run with a user that has geminiuser as its default
schema. On SQL 2000, this means that you should run as geminiuser.
I don't know which login you are logged in as, but this login needs to
map to geminiuser. Note that if the login geminiuser owns the database,
the login geminiuser maps to the user dbo.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx