April 26, 2010 at 7:10 pm
Hi,
I have recovered (from full backup) a database from host server. 90% of the tables belong to DBO schema and 10% of the tables and all stored procedure belongs to UserDefined Schema. After recovered, my ASP application is failing with Object not found when accessing UserDefined Schema objects. Both schemas were fine when running on hosted server. Can some one please advice. Database is SQL Server 2005 and size is around 250MB.
Your help in this regard is appreciated.
Thank you,
Sri.
April 27, 2010 at 6:04 am
Could be several issues. Do you have access to SSMS?
Check to make sure the recovery actually recovered the schema and the objects involved. Check to make sure the user hasn't been removed from SQL Server.
Check the code to make sure it's looking for "Schema.Object" instead of just "Object" (SQL Security always assumes current context first, DBO next). Speaking of which, double-check that the security context is the same as it used to be and doesn't need to be reset. That includes DDL triggers that might have altered permissions as well as normal SQL Security permissions.
My question is, why did you have to recover the database? What happened? This will likely lead you in the direction of your solution.
April 27, 2010 at 7:42 am
Hi Brandie,
Thank you for the response. I used wrong word Recovered instead of restored. We are moving from hosted place to dedicated server. I did backup the database using their control panel and RESTORED in our dedicated server. Those UserDefined SCHEMA objects are getting timed out. SPs are Pointing UserDefined Schema (ex. UserDefined.usp_FetchOrders). If I transfer UserDefined schema objects to DBO and application is working fine. I know that is not the correct way to do it. I want to know why is it not working with UserDefined and DBO mix objects. I have admin access to dedicated server. Please advice.
Thank you,
Sri.
April 27, 2010 at 7:55 am
Sounds like all you may have restored is the production database. Did you setup the logins/users as they were setup on the hosted system? It sounds like things may not be the same between systems.
April 27, 2010 at 8:16 am
Lynn Pettis (4/27/2010)
Sounds like all you may have restored is the production database. Did you setup the logins/users as they were setup on the hosted system? It sounds like things may not be the same between systems.
Lynn, you are thinking the same thing I am.
Also, after a restore, sp_change_users_login probably needs to be run against all the logins in that database to fix any broken SPIDs.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply