I am really struggling with a very simple task: copying a user database from a 2000 SP4 dev server to 2005 using the Copy Database Wizard. this database has just one object (a table owned by dbo) and just one user (dev2). This dev2 user is actually a Sql Server login that I created on the server beforehand with the same password that is on the source server.
Actually, my copy is somewhat successful: what happens is that the database copies over very nicely from 2000 to 2005 and I can query off of the one table using the sa account.
The problem is this: I cannot get the dev2 login to work. After I copy the database and log into the server using the dev2 login, the database is not valid. It does not recognize the database I have copied over.
So then I do the standard thing that I do to get this to work: attempt to delete the dev2 login (since it owns no objects) and then put it back in. But 2005 will not let me delete the login: it says that it "owns schema". But it does not own any schema! I checked stored procs, tables, etc.
So what is the right way to do this? I looked and didn't see the sp_help_rev_login procedure either.
Btw, detach and attach did not work any better either.
Any help would be much appreciated. I've never had this much trouble moving a 2M database!