Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Was DMN\Group a security group or a distribution group? –  James Jul 4 at 12:30

1 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.

share|improve this answer
    
Or what always fixes permissions when it comes to Windows and AD: reboot user's computer ;) –  Shawn Melton Dec 26 '11 at 17:34
    
What I mean by "not getting permissions" is that the permission path isn't showing up for that login when I run xp_logininfo. The user isn't a new member of that group (i.e. is an inaugural member of the group) nor is he a new employee. As far as I know, nothing has changed with his login for a very long time. The user can connect to the server, but is not receiving the permissions granted by the group. There are no explicit denies in place for that login or any user mapped to that login in a user database. –  Ben Thul Dec 27 '11 at 15:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.