I'm probably forgetting something basic, but right now I'm stumped.
We are moving a database to a new server. bcp'd out the login
accounts and bcp'd them into the new server. Restored the database
backup and everything was working fine yesterday testing out the
application -- this is a 3rd party application, not an in-house app
and database.

Today, the application server can't start. The log file shows that it
doesn't see a table. I recognize the table as one of the tables not
owned by dbo (3rd party, not my doing). I double check the value of
sysxlogins.sid and sysusers.sid, and sysusers.uid and sysobjects.uid
and everything matches ok.

If I log into Query Analyzer as the application user and select from
the table, I see the same error. If I use ownername.tablename where
ownername is the same as the login name I am using, then it works
fine. It's like SQL Server isn't recognizing the account as being the
same as the object owner.

I ran sp_changeobjectowner to change ownership to dbo and then ran it
again to change it back to the application account, and it still acts
like the object owner is not the object owner.

The quick solution was to change all objects to be owned by dbo -- I
checked with the vendor to make sure they didn't have any objections.
But I really would like to figure out why it was happening.

Has anyone had this problem? And, if so, did you know what I'm
missing?

Thanks
Teresa

Teresa Masino (teresa.masino (AT) peninsula (DOT) org) writes:
Did you use sp_helpuser? It's a good tool to find user/login mismatches.
A lot easier than looking at SIDs.

One possible explanation is that the login was the database owner on
the new server. In this case, the login does not map to ownername,
but to dbo.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx