I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management Studio).
All went OK but now I find I can't login to any of the databases using the SQL Server account that continues to work on the old RDBMS. My windows-authenticated login still works fine incidentally.
I had this idea the users and permissions would all be seamlessly duplicated onto the new database server but it seems something has gone wrong somewhere. I would appreciate comments/suggests/offers of help 😉
Best Answer
I see you already found a solution to your problem, one thing I noticed in your original question was that you still had access to the old server.
The following question on SO had a similar problem and includes links to a Microsoft article with a script to generate the user permissions.
https://stackoverflow.com/questions/461385/restoring-a-backup-to-a-different-server-user-permissions
(Resource listed for that question http://support.microsoft.com/kb/918992)
It sounds like changing the server setting from Windows authentication to Mixed mode authentication fixed your problem, but just in case it didn't completely resolve the problem I thought this might be useful.