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.