locked
orphand users RRS feed

  • Question

  • Hi Team,

     

    How to fix orphend user in MS SQ LSERVER 2005

     

    tx


    subu
    Friday, December 10, 2010 2:57 PM

Answers

  • This article explains the steps.
    Vishal Gajjar MCITP.DBA \ MCITP.DatabaseDeveloper
    • Marked as answer by WeiLin Qiao Monday, December 20, 2010 3:25 AM
    Friday, December 10, 2010 3:04 PM
  • Hi Subu999,

     

    As per Microsoft SQL Server Books Online:

     

    A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance.

     

    A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

     

    For more details, please refer the below links:

    Troubleshooting Orphaned Users

    Basic SQL Server Security concepts: SIDs, orphaned users, and loginless users

     

    Please feel free to ask if you have any questions.

     

    Thanks,
    Weilin Qiao


    Weilin Qiao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 13, 2010 9:04 AM

All replies

  • This article explains the steps.
    Vishal Gajjar MCITP.DBA \ MCITP.DatabaseDeveloper
    • Marked as answer by WeiLin Qiao Monday, December 20, 2010 3:25 AM
    Friday, December 10, 2010 3:04 PM
  • Hi Team,

     

    could you please guide me 

     

    Right know i am restoring database from one server to antother server here iam facting orphend user prob..

    what is orphend user ?

    What is use of these users ?

    In which senario we have to use orphand user s 

     

    last time also i am facing some prob with orphand user in LOGSHIPPING env also at that time L3 guys are take that issue 

    i am requesting you please plz guide on this.

     

     


    subu
    Friday, December 10, 2010 3:38 PM
  • subu

    ALTER USER uname WITH LOGIN =login

    Note the above command were introduced in SS2005 SP2

    http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, December 12, 2010 9:35 AM
  • Hi Subu999,

     

    As per Microsoft SQL Server Books Online:

     

    A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance.

     

    A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

     

    For more details, please refer the below links:

    Troubleshooting Orphaned Users

    Basic SQL Server Security concepts: SIDs, orphaned users, and loginless users

     

    Please feel free to ask if you have any questions.

     

    Thanks,
    Weilin Qiao


    Weilin Qiao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 13, 2010 9:04 AM
  • http://blogs.msdn.com/b/blakhani/archive/2008/02/25/script-map-all-orphan-users-to-logins-else-create-login-and-map.aspx
    {

    DISCLAIMER: SCRIPT IS PROVIDED "AS IS" WITHOUT WARRANTY REPRESENTATION OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THIS SCRIPT.


    }
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, December 13, 2010 9:20 AM