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.

  • 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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.