• SQLKnowItAll (3/22/2013)


    ScottPletcher (3/22/2013)


    SQLKnowItAll (3/22/2013)


    ScottPletcher (3/22/2013)


    IF you can very briefly drop the problem login(s) on your clients' machines, and immediately recreate them, you can fix this problem once and for all! Then you would avoid the on-going headaches from this.

    If you want more details, just let me know.

    I don't understand how dropping the logins and recreating them on a separate instance will guarantee the same sid as the login at the provider's machine. Plus, then you leave orphaned database users on the client's machine.

    You don't sync the SQL login SIDs on your development, QA and prod machines? Ouch; that must be a royal pita as you restore from one to another.

    Its not a PITA because during our refresh process we fix the orphans by running the script mentioned above through a cursor. I never thought of specifying the SID on creation, although our script that creates them does that automatically for us. I didn't see you mention above to specify the SID, which to the new user would be important 🙂 (Since they are probably using the GUI to do it) What I got from your post was that they would simply delete the login and then recreate it. I was trying to get you to be more clear for the newbie that comes to this post.

    On another note, I would not do this on the client's machines (assuming that is production), but on the restored machine. I spuppose we don't know which is which here.

    EDIT: IT is worth noting that we have 6 different environments and making sure all logins have a synced sid is sometimes not realistic. So that is why I am in the mindset of ALTER USER

    I thought I made it very clear that my post was just an overview of the idea; I didn't see any reason to flood the OP with details if they weren't interested in making, or couldn't make, the change. I'm sorry you weren't familiar with specifying the SID on a login create but, again, I didn't see the need for that type of detail if it wasn't going to be used in this case.

    In their case, I would think it would make sense to have the "master" copy to be on the instance used to develop the software. As they add clients, they can use their SID to create their login on the client instance (or make sure they use it in the script, or that the client knows to use their SID).

    Btw, I never use the GUI for anything in a production environment, except perhaps in a dire emergency.

    I always run a script after restoring to production, but if I'm very busy it may be a while in dev or QA, and the analysts/programmers will sometimes forget to do it when they run their own restores (yes, on dev and QA they can do their own restores). Vastly easier to have logins automatically sync up than try to later address orphan user errors :-).

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.