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 contosomultigroup belongs to two groups, as can be seen here in the output of whoami /groups:
CONTOSOgroup2 Group S-1-5-21-1304351064-1365540280-1243399584-1119 Mandatory group, Enabled by default, Enabled group
CONTOSOgroup1 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 [CONTOSOgroup1] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
CREATE LOGIN [CONTOSOgroup2] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
USE [testdb]
GO
CREATE USER [CONTOSOgroup1] FOR LOGIN [CONTOSOgroup1]
GO
CREATE USER [CONTOSOgroup2] FOR LOGIN [CONTOSOgroup2]
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 CONTOSOgroup1 WINDOWS GROUP GRANT OR DENY
269 0x01050000000000051500000058D1BE4DB87D6451A0C51C4A5F040000 CONTOSOgroup2 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 CONTOSOgroup1 WINDOWS GROUP GRANT OR DENY
6 0x01050000000000051500000058D1BE4DB87D6451A0C51C4A5F040000 CONTOSOgroup2 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 [CONTOSOgroup1]
GO
Then our login for CONTOSOmultigroup fails:
Login failed for user ‘CONTOSOmultigroup’. (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!