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'
;
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:
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;
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.
