Site icon SQLStarters

Do you know how many different types of users there are in SQL Server?

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.

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.

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.

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.

Hats off to anyone who knew (or remembered) of these last two.

Exit mobile version