7

I have an AD login which is in the below AD groups.

  • DOMAIN\GlobalGroup1
  • DOMAIN\GlobalGroup2
  • DOMAIN\GlobalGroup3
  • DOMAIN\UniversalGroup1

DOMAIN\UniversalGroup1 has been granted VIEW ANY DEFINITION on this SQL Server instance (10.50.6000.34). The user is not able to view any of the databases' definitions.

Other members of the DOMAIN\UniversalGroup1 appear to be able to view definitions.

EXEC xp_logininfo 'DOMAIN\user','all'

Only displays the 3 GlobalGroups. This is a documented limitation of the xp_logininfo procedure.

EXEC xp_logininfo 'DOMAIN\UniversalGroup1','members'

Confirms the user's membership to the group.

How can I troubleshoot this problem? Is the issue in any way related to the difference between the global and universal group?

  • Try impersonating the user and query sys.login_token. Also check to make sure UniversalGroup1 is actually a security group. – Kenneth Fisher Sep 19 '16 at 15:51
  • I had a similar issue, I connected to two servers having same permissions but could only see objects in one but not other. A restart fixed it for me. – Vladimir Oselsky Sep 19 '16 at 16:06
  • I've also seen an AD group created as a distribution group instead of a security group and had the same symptoms. If it's not showing up in login_tokens then SQL isn't seeing it for some reason. – Kenneth Fisher Sep 19 '16 at 16:19
  • Permissions in SQL Server are additive - if any of these groups have that permission denied to them, the user will be denied that permission, even though it's been granted as part of another group. That's another thing to check. – nateirvin Sep 22 '16 at 23:05
6

Is UAC enabled? UAC can truncate the user's list of group memberships

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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