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

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@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

.

Relevant Pages

  • Re: Orphan Indexes?
    ... >> Perhaps an owner issue for the table? ... >> Tibor Karaszi, SQL Server MVP ... >>> table is not the same as when I created the SP's in Query Analyzer. ... But now I have a major problem. ...
    (microsoft.public.sqlserver.programming)
  • Re: multiple cascade paths
    ... You should not have them if your schema is designed properly. ... make you understand that I tried to show that a proper design can have ... am a SQL Server MVP, for crying out loud - you can safely assume that I ...
    (comp.databases.ms-sqlserver)
  • Re: Need help on how to organize users and objects
    ... Yes Oracle is a bit different than Sql Server, but I think you will get the ... It doesnt become part of any schema until you assign it. ... a new developer started to work. ... need the COMMON database which stores tables with generic data and generic ...
    (microsoft.public.sqlserver.security)
  • Re: Qualifier und Owner ermitteln
    ... und ein Owner stehen. ... Hättest Du im gestrigen Threads die Links aufmerksam gelesen, ... Soweit es den SQL Server betrifft: ... "Trennung von Benutzer und Schema" ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: 3 Simple Security SQL Statements
    ... In SQL 2005, when you create an object, you specify the schema that the ... Kalen Delaney, SQL Server MVP ... Schema for a database role, that seems like the best setup. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (microsoft.public.sqlserver.security)