How to fix local windows user authentication after restoring master db to a new server?

Question

I have sql server that has local windows users as logins.

I want to create a duplicate of this setup in the development environment. The dev environment server will have the same computer name and will have the same local users created in windows.

If I script the logins and deploy them on the dev server, then the users will get orphaned since the login sid will not match the user sid in the db.

So instead, I am thinking about backup/restore of master db from the prod sql server into the dev sql server (both same version), to bring in the logins, followed by restoring the user dbs. This will ensure that dev server has same configuration settings and the logins have the same sid as the user databases.

However, I also realize that the actual sid of local windows user (in windows) on the new computer is different, and so the local windows login fails. To solve this problem I will need to script that login, delete the login, run the script to create the login, and fix the orphaned user. Have I got this right or is there another workaround?

• Charlieface: Why not use an AD domain with domain users, then the SID remains the same?
• variable: This question is specifically to cover the local windows user scenario
• Charlieface: You can just script the logins without the SID, CREATE LOGIN ... FROM WINDOWS and CREATE USER ... FROM LOGIN.
• variable: In that case also I will have to fix orphaned users. Because the restore db has the original user sid.

Answer 1

Did you try the ALTER USER ... WITH LOGIN ... command?

The documentation say that you can use this for not only SQL logins but also Windows users to make its SID match the desired login's SID.

• variable: Your answer is to alter the db user. But the login itself will be from the old server so I will need to delete it, recreate it, and fix the orphaned db user. No?
• Tibor Karaszi: Ah, yes I see now that you restore the master database. I typically don't do that. Yes, you have to re-create the login, since it points to the wrong SID. And then map the user to the right login.
• variable: To map, is using the ALTER USER command you have given above same as how sp_change_users_login works?
• Tibor Karaszi: Yes, those commands do the same thing. I prefer the newer command compared to the old deprecated command. That is why I recommended ALTER USER.