场景和初始问题
想象一下,您坐在以前从未使用过的任意 SQL Server 主机(传统 SQL Server,在计算机上;而不是 Azure)的键盘前。您拥有管理凭据,并且需要评估其数据库之一的安全配置。
- 您可以运行什么查询来确定该数据库中的用户是包含的数据库用户还是传统的基于登录的用户?
我对我的实际项目中经过 Windows 身份验证的用户特别感兴趣,但我通常对其工作原理感兴趣。因此,如果重要的话,请详细说明 SQL 验证与 Windows 验证。
测试
我尝试过创建包含的和基于登录的用户的各种方法(例如 SQL、SSMS UI),并探索了各种系统视图(例如sys.database_principals
,sys.sysusers
)和 SSMS 属性页,但无论我做什么,我都不知道如何事后确定用户是作为包含用户还是基于登录创建的。
一种可能的方法是查看是否存在与用户具有相同 SID 的登录。如果用户功能正常(例如能够连接)并且没有相应的登录,则必须包含该用户。
但是,反之亦然吗?如果存在具有相同 SID 的登录名和用户,这是否意味着该用户基于该登录名?让我们看一些证据。
首先,文档指出(底部的备注部分):
如果 master 数据库中有一个名为 name1 的登录名,并且您创建了一个名为 name1 的包含数据库用户,则当连接字符串中提供数据库名称时,在连接到数据库时将选择数据库用户的上下文而不是登录上下文。也就是说,包含的数据库用户将优先于具有相同名称的登录。
该文档未指定它是否适用于 SQL 身份验证、Windows 身份验证或两者。不过,如果我正确地阅读了文本,我们知道至少在某些情况下包含的用户和具有相同名称的登录可以共存。
但是,名称并不意味着它们是相同的;而是不同的。例如,我们可以拥有一个基于登录的用户,其名称与其基础登录名不同。我相信正是 SID 使它们变得“相同”。
那我们就试试这个吧。首先创建一个包含的数据库用户:
CREATE USER [CITRA\test];
我们可以作为该用户连接到数据库,因此我们知道它可以工作。当我们寻找相应的登录名时,正如所料,没有:
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'
;
现在,让我们基于同一 Windows 用户创建登录名:
CREATE LOGIN [CITRA\test] FROM WINDOWS;
当我们再次尝试将用户与登录名进行匹配时,我们找到了一对,因为它们具有相同的 SID:
这里刚刚发生了什么 - 我们是否有两个独立的事物,一个登录名和包含的用户,具有相同的名称和 SID,还是我们实际上通过事后创建登录名将包含的用户转换为基于登录的用户?
令人惊讶的是(对我来说)我认为是后者。如果我们禁用登录,我们将无法再连接 - 即使我们的用户最初是作为包含的数据库用户创建的(并且正常运行):
ALTER LOGIN [CITRA\test] DISABLE;
请注意,我们在连接中指定了目标数据库名称,如上面的文档摘录中所述。重申一下:
当连接字符串中提供数据库名称时,将通过登录选择数据库用户的上下文
不过,这并不是 SQL Server 在我们的测试中的行为方式。我唯一可以得出的结论(可能是由于缺乏想象力)是创建登录将我们包含的用户变成了基于登录的用户。
更基本的问题
上面的测试提出了一个比原来更基本的问题:
- 到底是什么让用户成为封闭用户或基于登录的?
从我在上面的测试中看到的情况来看,这实际上是匹配(通过 SID)登录的存在或不存在。此外,我们似乎可以通过创建或删除登录名来将现有用户在包含用户和基于登录名之间转换。至少对于经过 Windows 身份验证的用户来说是这样。
把想法大声说出来
如果这是正确的,我猜测“包含的数据库用户”甚至不是一个真正的东西 - 这意味着,在某个地方没有“IsContained”标志告诉我们用户是否被包含。
相反,我推测现代 SQL Server软件的行为通过连接时的行为体现了包含用户的概念。具体来说,新的 SQL Server 将验证“缺失”登录的用户(即包含的数据库用户),而旧版本的软件则不会。也许我只是还没有找到它,但似乎没有任何元数据明确告诉 SQL Server 包含用户;我认为它需要从环境的其他部分(例如sys.databases.containment
,存在/不存在登录)推断出来。
不过,我只是猜测这一点,而且我只考虑 Windows 身份验证(对于 SQL 身份验证的用户,密码仍然需要存储在某处)。如果您可以澄清最后一节中的想法,请这样做,但不要让它分散对前面几节中更实际问题的注意力。
感谢您的帮助。