This is a very common case: take a Windows user who belongs to multiple groups. Next, grant more than one of those groups as logins to SQL Server. The question then is: which permissions will that session inherit?

The answer it turns out is that the permissions will be additive and most restrictive. When a Windows login user authenticates to SQL Server, the login token is populated with ALL the groups that this user is associated with. You can view this information through the sys.login_token view. The information in this can be joined with sys.server_principals to get more information. At the database level, you can view the user token using the sys.user_token view.

Additive: Example

Here is a simple example of what I am talking about. Windows user contoso\multigroup belongs to two groups, as can be seen here in the output of whoami /groups:

CONTOSO\group2                   Group            S-1-5-21-1304351064-1365540280-1243399584-1119 Mandatory group, Enabled by default, Enabled group
CONTOSO\group1                   Group            S-1-5-21-1304351064-1365540280-1243399584-1118 Mandatory group, Enabled by default, Enabled group

We then grant these groups a login and map those logins to a test database:

USE [master]
GO

CREATE LOGIN [CONTOSO\group1] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

CREATE LOGIN [CONTOSO\group2] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

USE [testdb]
GO

CREATE USER [CONTOSO\group1] FOR LOGIN [CONTOSO\group1]
GO

CREATE USER [CONTOSO\group2] FOR LOGIN [CONTOSO\group2]
GO

Here is the information for the login token:

principal_id    sid    name    type    usage
2    0x02    public    SERVER ROLE    GRANT OR DENY
268    0x01050000000000051500000058D1BE4DB87D6451A0C51C4A5E040000    CONTOSO\group1    WINDOWS GROUP    GRANT OR DENY
269    0x01050000000000051500000058D1BE4DB87D6451A0C51C4A5F040000    CONTOSO\group2    WINDOWS GROUP    GRANT OR DENY

As you can see, the login token includes both groups. Next, let us view the user token within the TestDB database:

principal_id    sid    name    type    usage
0    0x01050000000000090400000083741B006749C04BA943C02702F2A762    public    ROLE    GRANT OR DENY
5    0x01050000000000051500000058D1BE4DB87D6451A0C51C4A5E040000    CONTOSO\group1    WINDOWS GROUP    GRANT OR DENY
6    0x01050000000000051500000058D1BE4DB87D6451A0C51C4A5F040000    CONTOSO\group2    WINDOWS GROUP    GRANT OR DENY

This should confirm what we talked about earlier – that the membership is additive.

Most Restrictive: Example

If we now proceed to deny login permissions to GROUP1:

DENY CONNECT SQL TO [CONTOSO\group1]
GO

Then our login for CONTOSO\multigroup fails:

Login failed for user ‘CONTOSO\multigroup'. (Microsoft SQL Server, Error: 18456)

Conclusion

The SQL Server engine security model is very powerful and flexible. At the same time it can be confusing in some scenarios. Hopefully, in this post I have cleared up an FAQ item around multiple group memberships for a Windows user; and how the effective permissions are both additive and also most restrictive.

That’s it for now! See you soon!