3

Scenario and Initial Question

Imagine you sit down at the keyboard of an arbitrary SQL Server host (traditional SQL Server, on a machine; not Azure) that you've never used before. You have administrative credentials, and need to assess the security configuration of one of its databases.

  • What query can you run to determine if a user in that database is a contained database user, or a traditional login-based user?

I'm specifically interested in Windows-authenticated users for my real-world project, but I'm generally interested in how this works. So, please elaborate on SQL- vs. Windows-authenticated, if it matters.

Tests

I've tried various methods of creating contained and login-based users (e.g. SQL, SSMS UI) and have explored various system views (e.g. sys.database_principals, sys.sysusers) and SSMS property pages, but no matter what I do, I can't figure out how to determine after-the-fact whether the user was created as contained or login-based.

One potential method would be to see if a login exists with the same SID as the user. If the user is functioning normally (e.g. able to connect) and there is no corresponding login, then the user must be contained.

But, is the converse true - if a login and a user with the same SID exist, does that imply that the user is based on that login? Let's look at some evidence.

Firstly, the doc states (Remarks section at bottom):

If there is a login in master database with the name name1 and you create a contained database user named name1, when a database name is provided in the connection string, the context of the database user will be picked over login context when connecting to the database. That is, contained database user will take precedence over logins with the same name.

The doc doesn't specify whether it applies to SQL-authentication, Windows-authentication, or both. If I'm reading the text correctly, though, we know that in at least some circumstances contained users and logins with the same name can coexist.

But, names don't make them the same; for example, we can have a login-based user with a different name than its underlying login. I believe that it's the SID that make them the "same".

Let's try this then. Start by creating a contained database user:

CREATE USER [CITRA\test];

We can connect to the database as this user, so we know it works. When we look for the corresponding login, as expected, there is none:

SELECT
    dp.name [user_name]
    ,sp.name [login_name]
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON
    dp.sid = sp.sid
WHERE
    dp.name = 'CITRA\test'
;

enter image description here

Now, let's create a login based on the same Windows user:

CREATE LOGIN [CITRA\test] FROM WINDOWS;

When we try again to match the user with a login, we find a pair because they have the same SID:

enter image description here

What just happened here - do we have two separate things, a login and contained user, with the same names and SIDs, or did we actually convert a contained user to a login-based user by virtue of creating the login after the fact?

Surprisingly (to me) I think it's the latter. If we disable the login, we can no longer connect - even though our user was originally created (and functioning normally) as a contained database user:

ALTER LOGIN [CITRA\test] DISABLE;

enter image description here

Observe that we are specifying the target database name in the connection, as described in the doc excerpt above. To reiterate:

when a database name is provided in the connection string, the context of the database user will be picked over login

That's not the way SQL Server is behaving in our test, though. The only thing I can conclude (perhaps from lack of imagination) is that creating the login turned our contained user into a login-based user.

More Fundamental Question

The test above begs a more fundamental question than the original:

  • What actually makes a user contained or login-based?

From what I see in the test above, it's literally the presence or absence of a matching (by SID) login. Furthermore, it appears that we can convert an existing user between contained and login-based simply by creating or dropping the login. At least for a Windows-authenticated user.

Thinking Out Loud

If this is correct, I'm guessing that a "contained database user" isn't even really a thing - meaning, there's no "IsContained" flag somewhere that tells us whether a user is contained.

Rather, I speculate that it's the behavior of the modern SQL Server software that manifests the concept of a contained user by virtue of its behavior at connection time. Specifically, new SQL Server will authenticate a user with a "missing" login - i.e. a contained database user - whereas old versions of the software would not. Maybe I just haven't found it yet, but there doesn't seem to be any metadata that tells SQL Server explicitly that a user is contained; I think it needs to infer it from other parts of the environment (e.g. sys.databases.containment, presence/absence of login).

I'm just guessing about this, though, and I'm only considering Windows-authentication (for SQL-authenticated users, passwords would still need to be stored somewhere). If you can clarify the ideas in this last section, please do, but don't let it distract from the more practical questions in the prior sections.

Thank you for your help.

3
  • What if you deleted the windows login? Would you be able to connect through that user? Commented Jun 20, 2023 at 6:25
  • If I'm understanding your question correctly, yes. In the scenario above, creating and disabling the login blocks the formerly-contained user from connecting; if I subsequently delete the login, the user is able to connect again.
    – manniongeo
    Commented Jun 27, 2023 at 20:16
  • That for me is an indication that the quote from the documentation is incorrect, and whether a Windows user is contained is based on the presence of a login with that SID. FWIW. Commented Jun 28, 2023 at 8:40

1 Answer 1

4

What actually makes a user contained or login-based?

A SQL user created in a contained database with the PASSWORD option is always contained. The authentication_type_desc column of sys.database_principals will show DATABASE and the contained database authenticates the user based on credentials stored in the database. A server principal (login) with the same name as the contained user is ignored.

The behavior of contained database Windows users isn't as straightforward. The CREATE USER syntax does not specify if the user is contained or not. The authentication_type_desc column of sys.database_principals will show WINDOWS regardless of whether or not the user is contained. The user is contained when no server principal exists with the same SID and the user is not a member of a Windows group with a login. Conversely, the user is not contained when a login with the same SID exists or the user is a member of a Windows group with a login.

do we have two separate things, a login and contained user, with the same names and SIDs, or did we actually convert a contained user to a login-based user by virtue of creating the login after the fact?

The implication with a Windows user in a contained database is that, unlike a contained SQL User, the user can be converted from/to a contained user by creating/dropping server principals.

I'm guessing that a "contained database user" isn't even really a thing - meaning, there's no "IsContained" flag somewhere that tells us whether a user is contained.

The authentication_type_desc DATABASE indicates a user is contained but one cannot determine if a user is contained when the value is WINDOWS.

3
  • Thank you for this concise and clear summary. Are there similar statements in the SQL Server documentation? Your description matches my observations and, clearly from your reputation you know of which you speak. Still, it would be helpful for me to see this directly from Microsoft. I don't know if this information doesn't exist, or if I just haven't found it yet.
    – manniongeo
    Commented Jun 27, 2023 at 20:30
  • Thanks also for describing the affect of Windows-group-based logins. I hadn't considered that before, but from testing see that the group-based login supersedes my contained user in the same way that a user-based login does. Specifically, creating a group login after creating the contained user allows me to see a list of all databases; also, revoking CONNECT SQL from the group login blocks the formerly-contained user from connecting to its database. It all makes sense now, but it is definitely not obvious.
    – manniongeo
    Commented Jun 27, 2023 at 20:34
  • 1
    @manniongeo, I also haven't found a MS doc reference that details Windows authentication behavior in the context of contained databases. The closest I've found is in the CREATE USER doc, which specifies users based on a Windows user/group that have no login authenticate at the database (assuming containment PARTIAL) whereas Windows users that have no login but are a member of a Windows group with a login authenticate at the instance level. It doesn't call out the implication I mentioned.
    – Dan Guzman
    Commented Jun 28, 2023 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.