Database Restore Causes Database User To Become Inoperable

  • Frequently, we will send out or receive a backup from our clients. We all have the same login and database user. When we restore the DB, we have to drop the database user and re-create him each time since our ASP.NET application user is not able to log into the database. He is able to log into the server, but not our database. As our client base increases this small little annoyance could really become a problem.

    Any ideas on how to be able to restore or backup the database in a way that enables the database user to stay valid and operable would be greatly appreciated.

    Thanks,

    Jim

  • That's because each login on a server has a sid that links them to the database user. When you restore the database from a different server, its sid is different from the login on your server and it can't link the 2. Run this to fix it:

    USE databaseName

    GO

    ALTER USER username WITH LOGIN = loginName

    That will change the sid of the database user to match with the login.

    Jared
    CE - Microsoft

  • 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.

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

  • 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.

    Jared
    CE - Microsoft

  • 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.

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

  • 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

    Jared
    CE - Microsoft

  • 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.

  • SQLKnowItAll (3/22/2013)

    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

    Hmm, not quite sure I follow. Unless you have a lot of server-level permissions, it's extremely quick and easy to adjust a SQL login's SID, and it's absolutely a one-time thing only per instance.

    It's not a major issue if you always have to run a script after every restore anyway, but I guess I prefer it because it can speed things up slightly when working on prod problems: just restore and go, no extra script to find and run ...

    And, as I noted before, I've got the other restores happening thing too ;-). There's just way too many instances and analysts+developers for me to personally restore all non-prod dbs, so I give them access to procs they can use to do much of it. They're supposed to run the sync'ing proc after restoring a dev or QA, but sometimes they don't :w00t:.

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

Viewing 8 posts - 1 through 7 (of 7 total)

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