A simple restore. SQL logins. You know what happens when you restore or attach a database from one server to another – mismatched SIDs. Now, the question that arose on my last lesson with the students was – what happens when you map them? Which SID is applied to whom – is the orphaned user “receiving” the login’s SID or is it vice versa? Let’s see how we can check this…
We have restored the database and we have a great candidate – our OrphanedUser is actually an orphaned user with SID ‘0x4DE46770BCCBCA42920A2A3CF6820822’! 🙂
OK, then what is the SID of the login?
Now let’s see which SID goes where? Let’s map them!
USE InaccessibleDB GO sp_change_users_login @Action='update_one', @UserNamePattern='OrphanedUser', @LoginName='OrphanedUser'; GO
If we now take a look at the login’s SID and the sid of the database user we see this:
I have to admit this was a great question! That’s why it is always fun to be around students – they will always ask things you would never ever (yep) even think of!