☰

使用映射到 SQL Server 用户/登录的 Active Directory 组的奇怪行为

tag-icon • tag-icon active-directory sql-server permissions database
使用映射到 SQL Server 用户/登录的 Active Directory 组的奇怪行为

我在 SQL Server 2008R2 上遇到了非常奇怪的行为。

我有一个充当 SQL Server 客户端的第三方应用程序。我有一部分用户应该有权访问此应用程序。我创建了一个 Active Directory 组，将其称为“DOM\appusers”，其中包含几个测试用户。我创建了一个同名的 SQL Server 登录名，创建了一个数据库，将其称为“appdb”，在 appdb 数据库上创建了一个与该登录名关联的用户“DOM\appusers”，并为该用户分配了“db_owner”角色。

大多数操作都有效。但其中“一些”会出现错误。特别是，一个查询失败：

SELECT x,y,z
  INTO tmpTable
  FROM (table1 INNER JOIN table 2 on table1.col1 = table2.col1)
       INNER JOIN table3 ON table1.col2 = table3.col2
 GROUP BY x,y,a,b,c
HAVING (((table2.col4)=0) AND
        ((table3.col5)=0)) AND
       ((table3.col6)=0)

这是从 SQL Profiler 中提取出来的，它标记了有错误的查询，但没有关于错误的更多详细信息，并且应用程序没有帮助，所以我无法确切地告诉您引发了什么错误。

我不是 SQL 专家，所以我不知道这个查询有什么特别之处，或者为什么它需要超出 db_owner 的权限，但这实际上不是一个关于 SQL 查询的问题。

对我来说真正奇怪的是，如果我绕过该组，我会得到不同的结果。如果我删除上面详述的 SQL Server 登录名和用户，然后直接创建一个引用 Active Directory 用户的新登录名和用户，并为其分配相同的 db_owner 角色，则上述查询有效。事实上，所有查询都按预期工作。

这很奇怪，我认为这一定是我的错误。我做了三次同样的实验，结果都一样。我验证了测试用户如果没有上述设置之一就无法访问数据库。我尝试将服务器范围的“sysadmin”角色分配给 AD 组，查询开始工作。显然，我不能就这样放任不管，但这是一个数据点。我还使用 SQL Server 身份验证而不是 Windows 身份验证创建了一个登录名，这也有效。

因此，显而易见的问题是：当用户通过组获得权限时，与“直接”获得权限时，权限有什么不同（就 MSSQL 的额外登录层和数据库用户而言，任何事情都是“直接”的）？db_owner 对组和对用户的意义不同吗？这只是一个错误吗？有解决办法吗？我不想将每个应用程序用户单独添加到 SQL Server 用户/登录列表中，但这是我目前想到的唯一解决方法。还有其他解决方案吗？

提前感谢你的帮助。:-)

答案1

事实证明，尽管我收到了错误消息，但这并不完全是一个“权限”问题。

长话短说，与 Windows 组关联的 SQL Server 用户没有默认架构，也无法配置架构。这是 SQL Server 2008R2（可能还有更早版本）的一个限制，已在 SQL Server 2012 中得到纠正。

因此，应用程序的查询试图在其没有权限的架构中创建 tmpTable。当我创建连接到单个 Windows 用户的 SQL Server 用户时，它之所以能正常工作，是因为在这种情况下，它分配了默认架构“dbo”，这是正确的架构。用户有权限在那里写入，一切正常。

最终，对我来说，唯一的解决方案是创建单独的 SQL Server 用户和与单独的 Windows 帐户关联的登录名。这是一个持续的维护麻烦，但对于此版本的 SQL Server 来说，这似乎是不可避免的。

答案2

我很确定问题在于服务帐户（您说它以“NETWORK SERVICE”的身份运行）没有查找域组成员所需的凭据。如果域用户经操作系统验证确实是 DOMAIN\user，则 SQL Server 会信任此用户，但如果 SQL Server 需要查找 DOMAIN\groupmember 是否确实是 DOMAIN\group 的成员，则此操作会失败，因为服务帐户没有查找此信息所需的权限。因此，组成员能够登录，因为操作系统表示没问题，但当它稍后检查对象的权限时，查找会失败。

尝试以域用户/域服务帐户的身份运行 SQL 服务，看看是否有帮助。

相关内容