Not all members in a Windows group have permissions

Question

I ran into a bit of an odd situation. Let's say I have a domain group [DMN\Group] which has been created as a login on my server and a user in the database. One of the members in that group is not getting the permissions that have been assigned to the group. What's odd to me about this situation is that when I do

exec xp_logininfo 'DMN\Group', 'members'

the user shows up. However, when I do

exec xp_logininfo 'DMN\user', 'all'

the group doesn't show up as a permission path. How can this happen? And, perhaps more importantly, how can I fix it?

Answer 1

Some ideas:

• The user has not logged off and back in after being added to the group. The group membership info for a user is stored in that user's login token so it may not show until they reauthenticate to the DC

• The user name is different to what you expect. Eg a Woman who got married and the login name changed to match the new surname. I've seen xp_logininfo fail because of this

• The user has multiple permission paths and is denied via one of them

• What do you mean by "not getting the permissions" is that "no rights to select" or "error when connecting" etc. To test, have the user log in using the ODBC control panel to test basic connectivity before worrying about permission

Edit, more ideas:

• The permission path for xp_logininfo only shows how they connected to the SQL Server instance. As per comment, the user in question can connect to the Instance but doesn't have permissions in the database. So, the other members of that group get database permissions from somewhere else.

I wouldn't get hung up on the output of xp_logininfo. I'd check permissions and mapping end to end and not bother with xp_logininfo personally

If the user is a member of multiple Windows groups of equal privilege level, only the group that was first granted access to SQL Server is reported.