I need to restore a DB from an SQL Server instance to another and I am well aware that the DB users will be transferred but the logins won't. I have created a script to be able to create the logins on the destination SQL Server instance, including the password hashed and the login SID. However, I am uncertain if it makes a difference whether the logins are created on the destination instance prior to the DB restore or after the DB is restored.
If the logins are created first and then the DB is restored, the DB users will map with the existing corresponding logins accordingly due to SID, however, if I do the DB restore and create the logins afterwards, will they still map automatically or will one need to do an ALTER USER WITH LOGIN or similar?
Best Answer
It doesn't matter when the logins are created. As long as the SIDs match, the database user will be associated with the login.
Every time a user attempts to connect to a database, SQL Server is going to check for a mapped login (unless it's a contained database). It does this by checking to see if there is a login with a SID that matches the user's SID. Accordingly, it doesn't matter when the login was created because it will be matched at the the time the user attempts to connect to the database.