"Table does not exist" after moving database to another server 1

[cceng]

I just finished moving 4 databases to another server. We have an application that does some manipulation of the data in these databases. When we run this application it comes back with an error "Tabel does not exist.....invalid object name DBA.UDEF"

I am guessing this has to do with the links when this database was moved. Any suggestions on how to fix this.

I am about as much of a newbie as you can be to SQL.

Thanks!

 

[AshleyRickards]

I'm assuming in the code there are references to servername.databasename.owner.object. Check the references to the server and database. You will need to change them to the new server name. Ashley L Rickards
SQL DBA

 

[cceng]

The server name hasn't changed and neither has the users. Unless caps makes a diff?

 

[AshleyRickards]

Hmmm .. No it's not case sensitive. What type of application? Ashley L Rickards
SQL DBA

 

[tlbroadbent]

How did you transfer the databases? By chance, were the objects created with owner 'dbo' rather than 'DBA' listed in your post? Depending on method used, the objects may have been created with a different owner if the original owner was not 'dbo.' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[aalmeida]

when you run a select * from sysobjects can you verify that the table you are looking for exists? if yes can you see who the table owner is? AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[cceng]

It does show that the table exist and that the owner is dba. I am not sure what the app is trying to do. I moved these db's using the sp_detach and attach commands.

 

[tlbroadbent]

Have you validated that login is opening the correct database? If you transferred the login or created a new login, it may open a different default database on the new server. Of course, you application may be establishing a connection and explicitly naming the database in which case my question is meaningless. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cceng]

I wish I could say what the application is doing. This is a third party app that we did not install in our enviroment. Out of the four databases I moved only this one isn't working.

It appears to login ok. Is it possible that permissions could have to do with it?

 

[aalmeida]

You stated that you had moved this Db to another Server, it may be that the logins needs to be remaped and permitions needs to be reset, also some times indexes needs to be fixed, I would go through the loggins and see if they are functionals specialy the DBA login AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[cceng]

What is the best command for me to do this. I tried the "fix lofins" and I get an error "This action 'auto_fix' is incompatible with the other parameter values ('(null)','(null)').

Any ideas on this?

 

[aalmeida]

You actually have to one by one, most times you will have to use sp_revockbdaccess 'loginname' and recreate it. I know it is a little cumbersome, but it is necessary when you change servers AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[cceng]

Will this work if the user is the owner of objects? Last timeI tried to remove the user I couldn't. If I need to change ownership how can i do it.

Thanks!

 

[aalmeida]

to change objects ownership you have to use sp_changeobjectowner AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[tlbroadbent]

If you suspect that the logins and users don't match because of the transfer, check this link for information abouut fixing the logins and users.

http://www.swynk.com/friends/boyle/fixingbrokenlogins.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cceng]

Thanks,

I have tried that and I get an error back saying..

"This action 'auto_fix' is incompatible with the other parameter values ('(null)','(null)')"

 

[tlbroadbent]

I assume you are referring to the sp_change_users_login stored procedure. The auto_fix option requires a user name.

Example:
exec sp_change_users_login auto_fix, dba

You can run a report without user names to see if the SP can identify any possible problems.

Example:
sp_change_users_login report Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cceng]

When I run the report action it does not show my user dba, yet I see him in the users.

 

[tlbroadbent]

The user is probably OK then. Have you checked the login to make sure it is set up with access to the database and that database is its default DB?

Run sp_helpuser in the database to get info about the user.

sp_helpuser dba

Try running sp_helplogins to get additional info about the login.

/* use login name if different than user name */
exec sp_helplogins dba

The first result set form the SP wil show the default database under the column heading, DefDBName. The second result set will show all databases and user names as well as roles in the databases. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cceng]

When I run sp_helpuser dba I get:

UserName GroupName LoginName DefDBName UserID SUserID
-------- ---------------------------- --------- ------------ ------ -------
dba db_owner dba master 5 10
dba db_accessadmin dba master 5 10

When I run sp_helplogins dba

LoginName SID DefDBName DefLangName AUser ARemote
--------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ -------------------- ----- -------
dba 0x711734C3AFAFD5118C0E0008C75DCFB4 master us_english yes no

(1 row(s) affected)

LoginName DBName UserName UserOrAlias
--------- -------- ---------------------------- -----------
dba tman db_accessadmin MemberOf
dba tman db_owner MemberOf
dba tman dba User

(3 row(s) affected)


It doesn't aoppear to me as anything is wrong.