SQL Server: Windows Groups, default schemas, and other properties

Exceptions are dangerous because people like to simplify their thinking process using rules, so exceptions always carry the risk of being overlooked. In security, exceptions are a bad thing because they make the model more complex and complex systems can break in more ways than simple systems, thus being harder to analyze and secure.

Windows Groups are an exception in the SQL Server security model. You can quickly infer my opinion about them from the above paragraph. At Windows OS level they behave similarly to how roles behave in SQL Server - they are simply containers of permissions and privileges. But bringing them in the SQL Server world creates a hybrid - an entity that can not only be granted permissions, but can also own objects as well - a mix of roles and logins/users. In SQL Server, Windows Groups are a secondary identity with additional capabilities that are traditionally reserved only for primary identities. Suddenly, Windows Groups require handling not seen in any other security system.

From a usability point of view, it is convenient to get access to a system via membership to a Windows Group, but from a security perspective, the actual user identity needs to be always available and must not be allowed to disappear behind the group anonymity. Imagine having to explain who deleted an important table - "Builtin\Users did it!" This leads to tradeoffs that are either not noticed or that surprise those that pay attention. For example, even though groups can own objects, an object created by someone that connected via a group membership will be owned by a login or user created under the cover - this is called implicit login/user creation and is done so that the identity of the object creator is not lost.

As far as groups work like they work in the Windows OS or like roles work in SQL Server, their behavior is easy to understand. This is because the semantics of permissions allows them to be added together and still make sense. So, if I belong to two different groups and I inherit from them two different sets of permissions, there is no confusion about what permissions I have - I simply have all the GRANT's and DENY's that those groups provide me with.

But the situation gets trickier when we want to endow groups with properties similar to what "real" principals have. The earliest situation consisted of the default language and default database settings that were added to logins. Because groups were supposed to act as logins, a solution was needed for them too. I was not around at the time these options were introduced, but I can see the result: the options were simply added to Windows Groups as well. However, the problem with having these options available for groups is that they really reflect properties and properties, unlike permissions, are not additive. If I am a member of two groups and each has a different default database, which one do I end up connecting to? Having these properties for groups was a bad choice, but now this cannot be fixed because of backward compatibility.

A new problem these days is that schemas were introduced in SQL Server 2005 and with them, a new property was added to users: DEFAULT_SCHEMA. However, the bad choice made earlier was not repeated this time and users mapped to Windows Groups were not allowed to have this property set. This causes problems with the use of Windows Groups as it is indeed desirable to be able to specify default schemas for Windows Groups, but this must be done in an unambiguous way and there is no mechanism allowing this today. Addressing this problem is currently an open issue.

Some take-offs from this discussion:

Windows Groups can simplify management but due to their hybrid nature, they come with some restrictions. If you want to use them to avoid managing a large set of logins and users, then make sure you don't provide them with permissions to create objects - object creation will trigger implicit login/user creation, defeating the initial reason to use them. If you avoid the object creation, there is still the issue of not being able to control the default schema - until a solution is provided, access to data via Windows Group membership needs to be done using schema qualified object names, Finally, Windows Groups are useful today because they provide a way to authenticate to SQL Server and can carry permissions, but they are not yet ready to be used as carriers of properties.