The answer to this question is not 2, happy and unhappy users. This time we’re discussing security, not the end user experience of your perfectly tuned databases.
I was recently having a discussion around SQL Server security, more specifically about the logins and user. The question I was asked after going through couple examples was, that how many different types of users there actually are. While this seems like a trivial thing to answer, I have to admit that even after thinking about this for a little while, and still got the answer off by 2. As it turns out there’s bit more complexity to this than might be obvious at the first glance.
Without checking from anywhere, can you name all the different types of users? Read onward to find out.
The correct answer is 12! I managed to remember 10 out of these, the ones I forgot are pretty weird ones and I have admittedly never ever used them (I don’t know if anyone has?).
Here’s the complete list of all the users.
Users created from login residing in master database
This is by doubt the most common type of user, for this you create a login into master database which is based on one of these.
- Login created with SQL Authentication
- Login created from AD user account
- Login created from AD security group
The next category is bit more recent addition.
Database contained users
This requires you to use contained database feature in on-premise SQL Server or Azure SQL Database. In this, the user is created directly into the database without mapping it to a login, it’s a great way to keep databases portable as you have no need to syncronize logins between instances.
- User created from Azure AD user
- User created from Windows user
- User created from Windows group
- Database user with password
I’ve occasionally seen these confused with orphaned users, which are a totally different case. Next we have some special users, because they can’t authenticate.
Users that can’t authenticate
At first you might wonder why in the world you’d like to have such users, but there are few use cases for these. I’ve occasionally used the one without a login to grant limited permissions for Stored Procedures. Other use cases exist as well for these, such as module signing.
- User without a login
- User created from certificate
- User created from asymmetric key
And then finally we have the two cases of users that I didn’t remember.
Users created from AD account with permissions from groups
I had completely forgotten that this option even existed, but this is something you can also do. I don’t really know why you’d do it, because to me this looks odd cousin of a contained user, similar but not the same.
- User created from AD account without a login, that can connect to SQL Server by authenticating as a member of AD group that has a login
- User created from AD group without a login, that can connect to SQL Server by authenticating as a member of a another group that has a login
Hats off to anyone who knew (or remembered) of these last two.