locked
Login Not possible RRS feed

  • Question

  • I restored a database from another server to a new server. In the security found that the user was also restore.

    Hence I created the corresponding login for the user with the default database as the restored database.  But when I tried to Login thru Sql server management studio an error was shown saying that login failed as the default database not accessible. Why this error and what is the solution to this problem.

    Thanking you in advance

    Binny Mathew.

    Tuesday, April 1, 2014 7:52 AM

Answers

  • This is typical orphan user problem.

    http://technet.microsoft.com/en-us/library/ms175475.aspx

    sp_change_users_login is your friend.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, April 1, 2014 7:54 AM
  • Root cause of the issue is that SID for Login and Users is not same and hence not mapped.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, April 1, 2014 7:55 AM
  • You need to map the login with the user.

    Maps an existing database user to a SQL Server login. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.

    Ref: http://technet.microsoft.com/en-us/library/ms174378.aspx

    You need to change your default database for the login as well.

    alter login login_name with default_database = yourdb

    Tuesday, April 1, 2014 8:00 AM

All replies

  • This is typical orphan user problem.

    http://technet.microsoft.com/en-us/library/ms175475.aspx

    sp_change_users_login is your friend.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, April 1, 2014 7:54 AM
  • Root cause of the issue is that SID for Login and Users is not same and hence not mapped.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, April 1, 2014 7:55 AM
  • You need to map the login with the user.

    Maps an existing database user to a SQL Server login. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.

    Ref: http://technet.microsoft.com/en-us/library/ms174378.aspx

    You need to change your default database for the login as well.

    alter login login_name with default_database = yourdb

    Tuesday, April 1, 2014 8:00 AM