Authentication Archives - Boris Hristov

On 10th of July I delivered a 3-hour seminar at the biggest IT University in Bulgaria – SoftUni. It was an incredible experience and I am still blown away by the fact that this organisation did not even exist some months ago! This place is absolutely revolutionising the way how IT education is done and it was a pleasure for me to be part of this process! I talked to the students about what SQL Server is and I also shared some practical advices on what they should be careful about when they plan, install and configure the product. We also touched on how the authentication works and how can they create and assign the needed permissions for their applications. My slide deck is below, but I have to say we needed it for just 50% of the time. During the other 50% we were hands on with our VMs!

Thanks again to all who participated and stayed till the late hours of the day! I really hope you enjoyed it and yes, I will see you again soon!

First Steps with Microsoft SQL Server from Boris Hristov

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:

So, obviously, what happens is that the login’s SID is used and “received” by the database user in order for them to be successfully mapped.

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!

Speaking at SoftUni, Sofia

What happens when you fix the mismatched SIDs?