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 ;-)

5 Answers 5


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.

  • I'm sure this wont' be the first time I tussle with SQL Server over users/permissions so i'll bookmark the article for posterity. Many thanks for posting.
    – immutabl
    Commented Mar 23, 2011 at 16:49

This is known as "orphaned users". Here are 2 ways to fix it

  1. 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

  2. If you use Windows logins only, then you can run this per database to generate a script


    [type] IN ('G', 'U')
  • Thanks for your response - my web app uses SQL logins while our windows accounts are used for administration purposes. I can't see a way to restore as 'loginsource' as you suggest - could you please elaborate? I'm hoping this can all be done using Managment Studio?
    – immutabl
    Commented Mar 23, 2011 at 14:57
  • PS. I followed this doc: support.microsoft.com/kb/274188 and ran all the scripts (apparently) successfully. But nothing has changed.
    – immutabl
    Commented Mar 23, 2011 at 15:20

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)
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
INTO @username
    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.

  • Thanks for the script. Can I assume there's no way of doing this in Management Studio? Also, some people are suggesting I delete and recreate the DB-level users - is this recommended?
    – immutabl
    Commented Mar 23, 2011 at 15:35
  • This assumes logins already exists and only mataches SIDs
    – gbn
    Commented Mar 23, 2011 at 15:36
  • doesn't sp_change_users_login update the SID? msdn.microsoft.com/en-us/library/ms174378.aspx Commented Mar 23, 2011 at 15:40
  • 5arx - there is no way to do this in SSMS. As i said, in an ideal situation you would have scripted your permissions before the restore. At this point you either need to recreate by hand or try to manually piece things together yourself. if i were you, i would restore the old db, script out the permissions, do the restore from the other server, and then redo your permissions. Commented Mar 23, 2011 at 15:42
  • "Update_One: Links the specified user in the current database to an existing SQL Server login. user and login must be specified. password must be NULL or not specified." So it assumes logins exists already.
    – gbn
    Commented Mar 23, 2011 at 20:08

You can refer the following URL to fix the database user permissions



Here is a quick article that explains the solution:

Restoring Orphaned Logins in SQL Server After Restoring Database

  • Fixed the link to the article
    – Khattab
    Commented Mar 6, 2017 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.