Re: No access at sql 2000 DB after restore on an other sql server
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sun, 27 May 2007 21:43:54 +0000 (UTC)
flabs (flabs@xxxxxxxxxxxxxxxxxxxxxxxxx) 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
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
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@xxxxxxxxxxxxx
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
.
- Follow-Ups:
- Prev by Date: Re: Permission to view system tables in master database
- Next by Date: Re: No access at sql 2000 DB after restore on an other sql server
- Previous by thread: Permission to view system tables in master database
- Next by thread: Re: No access at sql 2000 DB after restore on an other sql server
- Index(es):
Relevant Pages
|
