0

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?

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

1 Answer 1

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

4
  • 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?
    – variable
    Sep 9, 2022 at 10:45
  • 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. Sep 9, 2022 at 13:15
  • To map, is using the ALTER USER command you have given above same as how sp_change_users_login works?
    – variable
    Sep 9, 2022 at 15:06
  • 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. Sep 11, 2022 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.