Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have 2 SQL Servers, let's call them A and B. I have a ColdFusion app that runs on server A. It connects to my database, MyDb. I needed to update the db content so I backed up the same db on server B and restored it in server A (I backed up the server A's MyDb first before doing the restore) I have verified that the connection in the CF Datasources is still OK. When I ran my CF app in server A, I got the Invalid object "tablename" error. The database user has a user mapping of db owner in the said database.

Curious, I restored server A's MyDb backup to server B's MyDB and I was able to connect. No error was encountered.

Any idea on this?

share|improve this question
1  
Each SQL Server user has a hidden internal ID that is separate from the user "name". Try going into Database\Security in SSMS and removing, then adding back the same "user name" – 孔夫子 Apr 5 at 9:14
If this is a SQL login, they most likely have different SIDs on the two servers, unless you explicitly specified it in the CREATE LOGIN statement. Either reassociate the user to the login, or recreate the login with the same SID as on the old server. – Jon Seigel Apr 5 at 15:40
I tried both and did not work. Even if I tried restoring the same db on SERVER B on a new db, same error exists. – chiemen Apr 10 at 3:55

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.