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 😉
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.
(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.
This is known as “orphaned users”. Here are 2 ways to fix it
If you can, restore the original master database as “loginsource” and sys.server_principals has enough info to generate all SQL Server and Windows logins. That is, the SIDs and encrypted password
If you use Windows logins only, then you can run this per database to generate a script
SELECT 'CREATE LOGIN [' + SUSER_SNAME(sid) + '] FROM WINDOWS' FROM sys.database_principals WHERE [type] IN ('G', 'U')
Ideally you would script out the users and permissions prior to doing the restore. If that has not happened, then you need to go about fixing things after the fact, and chances are something will be missed but you should be able to get about 90% of the way there.
The first thing you need to ascertain is if the same logins exist on the new server. If they don’t then you should find out if it is OK for the logins to be created on the new server. Never assume that they should be created, there could be a good reason as to why they did not exist in the first place. You can then go about creating them by digging through the sysusers table.
You can fix the orphaned users by running something similar to the following:
DECLARE @username varchar(25), @loginsid varbinary(85) DECLARE fixusers CURSOR FOR SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null and name in (select name from master..syslogins) ORDER BY name OPEN fixusers FETCH NEXT FROM fixusers INTO @username WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_change_users_login 'update_one', @username, @username FETCH NEXT FROM fixusers INTO @username END CLOSE fixusers DEALLOCATE fixusers
This code will work for SQL2008, but was written to be backward compatible for SQL2000.
You can refer the following URL to fix the database user permissions
Here is a quick article that explains the solution: