In my last post I wrote about the great things that xp_logininfo can do for you, like tell you if a user account has access and if so the permission path for that access. It also allows you to find out the membership of groups that have logins.
There’s a problem with xp_logininfo however. If you attempt to query a group that contains a group you won’t get any of the information around the members of that second group. It’s a serious limitation of xp_logininfo.
Take the following example:
Here we have three users and a group.
If we query this we expect to see members of all the groups, however as xp_logininfo is not recursive we only get the top level group membership.
xp_logininfo 'awesomesauceDBAs', 'members'
I see this as an issue, as such I’ve created Connect item 718737 to get this fixed. Please go vote and help make SQL Server better.