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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

flabs - 27 May 2007 22:15 GMT

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 the
table exists. the same query on the original db Server is ok i recieved the
right result
whats wrong?
Many Thanks for your help

Reply to this Message

Erland Sommarskog - 27 May 2007 22:43 GMT

> i've some trouble with a sql server 2000 db.
> the db uses sql server auth
[quoted text clipped - 13 lines]
> 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.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

flabs - 27 May 2007 23:32 GMT

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.

> > i've some trouble with a sql server 2000 db.
> > the db uses sql server auth
[quoted text clipped - 28 lines]
>
> This should give you the owner/schema for the issues table.

Reply to this Message

Erland Sommarskog - 28 May 2007 23:15 GMT

> no, i didn't run sp_changedbowner. why must i run the sp ?
>
[quoted text clipped - 13 lines]
> 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.

Signature

Reply to this Message