Post reply

how to fix the orphaned users problem

  • February 17, 2012 at 7:44 am

    #255921

    How to fix the orphaned users problem in sql server 2008.

  • February 17, 2012 at 7:55 am

    #1448522

    take a look at "sp_change_users_login" and "alter user"

  • February 17, 2012 at 8:24 am

    #1448558

    I tried but it is not working

  • February 17, 2012 at 8:25 am

    #1448559

    what have you tried, whats not working and what errors are you getting?

  • February 17, 2012 at 8:33 am

    #1448567

    Nice avatar Anthony 🙂

    Sujeet Singh

  • February 17, 2012 at 8:38 am

    #1448571

    gantavasu (2/17/2012)

    How to fix the orphaned users problem in sql server 2008.

    It seems you are asking it for some interview preparation from the way you are asking it 😉

    Please accept the suggestion of Anthony & have a look on SP_Change_Users_Login & ALTER USER commands on MSDN. It is well explained there with examples. If you don't understand anything from that, you may ask the question here then.

    Sujeet Singh

  • February 17, 2012 at 8:41 am

    #1448573

    gantavasu (2/17/2012)

    How to fix the orphaned users problem in sql server 2008.

    Have a look on this article.

    http://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/

    java

  • February 17, 2012 at 9:00 am

    #1448589

    Divine Flame (2/17/2012)

    Nice avatar Anthony 🙂

    thanks, its an animated gif, but some reason the animation is not happening.

  • February 18, 2012 at 3:02 am

    #1448880

    Sorry not like that i have moved the database from one server to another server here i am getting this problem. and also i don't have the exact idea about the orphaned users problem. that is the case i have posted here

  • February 18, 2012 at 10:39 am

    #1448918

    gantavasu (2/18/2012)

    Sorry not like that i have moved the database from one server to another server here i am getting this problem. and also i don't have the exact idea about the orphaned users problem. that is the case i have posted here

    Couple of questions

    has the database with the orphaned users come from a previous version of SQL Server?

    do you want the logins original passwords retained or can you use new ones?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 19, 2012 at 11:15 pm

    #1449075

    Sp_change_users_login 'report'

    This gives you the list of orphaned users in the database.

    sp_change_users_login 'auto_fix','orphaned users'

    this will fix the orphaned user in the database.

    Cheers !!!!

    ---- Babu

  • February 20, 2012 at 1:18 pm

    #1449390

    Maybe you decided not to read the article posted in earlier posts because you like this forum better. So, here you go. Each user that is set up in SQL Server on an instance is given a unique id. Even though user 'jared' is on instanceA and instanceB, this user has a different unique id associated with it that was generated at the time the user was created. This way, if we change the users name from 'jared' to 'jkarney' we don't have to go and update every place where 'jared' exists. Similar to why we so this in normal database design. When you restore a database from another instance, even if it has the same user names, it's stored ids are different. So they are out of sync. This is "WHY" you have to identify and fix orphaned users.

    Jared
    CE - Microsoft

  • February 21, 2012 at 3:14 am

    #1449543

    sp_change_users_login is going to be deprecated

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

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • February 21, 2012 at 5:43 am

    #1449633

    Sachin Nandanwar (2/21/2012)

    sp_change_users_login is going to be deprecated

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

    Yes, but this is a 2008 forum and we should still use the tools given to us for the time being. I don't see "many" people using this sp in their scripts, it is typically a manual thing. So why not use it?

    Jared
    CE - Microsoft

  • February 21, 2012 at 5:58 am

    #1449644

    SQLKnowItAll (2/21/2012)

    Sachin Nandanwar (2/21/2012)

    sp_change_users_login is going to be deprecated

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

    Yes, but this is a 2008 forum and we should still use the tools given to us for the time being. I don't see "many" people using this sp in their scripts, it is typically a manual thing. So why not use it?

    granted that yes it is going to be deprecated, and granted to Jared as well, but we also give the new syntax with ALTER USER statement

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply