April 8, 2008 at 9:00 pm
I recently migrated a SQL 2000 db to a different server. from USSERVER1 to USSERVER2 both servers have exactly the same SP's and builds.
Both databases are named exactly the same including the table owners. I did a backup and restore from USSERVER1 to USSERVER2.
On USSERVER2 SELECT COUNT(*) FROM wmError will not work, but works on USSERVER1.
On USSERVER2 SELECT COUNT(*) FROM WEBM.wmError will work. I can't use the 2nd method because of existing coding.
Does anyone know why? And how I can fix it?
April 9, 2008 at 9:17 am
Apparently, wmError is owned by the schema WEBM in the USSERVER2 database. You say the owners are the same in both databases. Do you connect to BOTH servers as WEBM? If the user running the query is someone other than the table owner, the owner schema must be specified.
Greg
April 9, 2008 at 11:47 am
i am logged in as WEBM, and the the table schema is the same.
April 9, 2008 at 4:57 pm
Is WEBM a SQL Server login? Did you re-sync the database users with logins on USSERVER2 after restoring the database? Try running sp_change_users_login 'report' in the database and see if it returns WEBM.
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply