locked
cannot login to the database RRS feed

  • Question

  • I have restored a database from one server to another server. After I migrate the users complain that they cannot login to the database. How will I trouble shoot

     

    Thursday, November 20, 2008 4:29 AM

Answers

  • Unless you have restored the master database, your users may not have valid logins on the new server. Remember, even though the database has users they also need a valid login to the sql server to be able to access the database.

     

    You can transfer logins using SSIS or you can recreate the logins manually.

     

    If they are SQL Logins then it is highly likely you will need to remap the logins to the users are the SIDs will be out of sync (this will not be the case if its a Windows domain login. You can do this by using the system stored procedure sp_change_users_login.

     

    http://msdn.microsoft.com/en-us/library/ms174378(SQL.90).aspx

     

    HTH!

    Thursday, November 20, 2008 4:30 PM

All replies

  • What's the error users are getting?

     

    Thursday, November 20, 2008 6:25 AM
  •  

    What does "migrate the users" mean ? The logins may need to be reconnected again to the database users, that is probably the problem.

     

    http://www.mssqltips.com/tip.asp?tip=1063

     

    Jens K. Suessmeyer

    Thursday, November 20, 2008 8:49 AM
  • Unless you have restored the master database, your users may not have valid logins on the new server. Remember, even though the database has users they also need a valid login to the sql server to be able to access the database.

     

    You can transfer logins using SSIS or you can recreate the logins manually.

     

    If they are SQL Logins then it is highly likely you will need to remap the logins to the users are the SIDs will be out of sync (this will not be the case if its a Windows domain login. You can do this by using the system stored procedure sp_change_users_login.

     

    http://msdn.microsoft.com/en-us/library/ms174378(SQL.90).aspx

     

    HTH!

    Thursday, November 20, 2008 4:30 PM
  • Thank you. I didn't restore the master DB. Now is working. Thank you

     

    Friday, November 21, 2008 3:03 AM