domain user belonging to multiple windows group

If I have a domain user DOMAIN\user1 who belongs to multiple window groups say DOMAIN\LookupConfigUsers and DOMAIN\AuditConfigUsers. In sqlserver, I would create two logins - DOMAIN\LookupConfigUsers and DOMAIN\AuditConfigUsers and matching users in the database. Then I grant LookupConfig role to the LookupConfigUsers user and AuditConfig role to the AuditConfigUsers user in the database. When DOMAIN\user1 logs in, will it have both roles? I try to set this up but it does not seem to work. The domain user only picks up one of the role. Am I on the right track? If not, what is the proper way to grant multiple roles to a user when it belongs to multiple groups and each group has different privileges in the database.

Based on the scenario you described, DOMAIN\user should be a member of both roles ( LookupConfig role & AuditConfig role). You can use the following script to take a look to the login and user token on the current connection:

SELECT name, type, usage FROM sys.login_token ORDER BY type, name

SELECT name, type, usage FROM sys.user_token ORDER BY type, name

go

In the login token you should be able to look at all the server and Windows groups that are part of the token, while on the user token view you should be able to see the database roles and Windows roles that are part of the context.

The usage column will tell you how each row is used, typically it should be “GRANT OR DENY”, which means that row is being used to evaluate for both granted and denied permissions, while a “DENY ONLY” value means that only denied permissions will be honored.

Please let us know the results of this query. If you see both roles, my guess would be that either LookupConfig or AuditConfgi role has an explicit denied permission that is affecting the permission check.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

It works now. It's actually my fault. I had one group defined initially and later when I add the other group, I simply disconnect and reconnect from the Management Studio. It did not pick up the new group. Today, I tried closing the Management Studio and reopen it, and it picks up both groups and roles. Your sql statements above showed me exactly what I expected - domin\user belongs to both LookConfig and AuditConfig Role.

However, I don't understand why it also belongs to the public role?

Also, the login_token output names like -

NT AUTHORITY\Authenticated Users, NT AUTHORITY\INTERACTIVE, NT AUTHORITY\NTLM Authentication, domain\None, \Everyone, etc...

What are these groups?

I am new to sqlserver and windows authentication. Any help would be much appreciated.

|||

No problem, we are happy to help.The public role is a role that everyone in the system belongs to, think of the public as “Everyone”. If you grant any permission to public, everyone will get it. This behavior is automatic and cannot be changed.

Regarding the other Windows groups in the login token. To generate the SQL Server login token we take the token from the Windows client connection and get every group from it. You can double check it in Windows using the whoami tool (i.e. whoami /groups).

I hope this information helps. Let us know if you have any further question, we will be glad to help.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine