Use xp_logininfo to retrieve AD group members list

I was discussing with one of my colleague and during the discussion he told me that he is not able to view the list of members available in a AD group since he dont have permission on AD forest. Normally to retrieve this he will sent the request to the AD team and they will be verifying or sending him the list. I informed him that we have an alternate (happy news to him) to use xp_logininfo SQL extended stored procedure to retrieve the list, he has used it and got the desired result. I then thought of putting it in blog since in most of the big companies DBAs will be limited to the permission so she need to check with the respective team to retrieve the settings. This actually needed when an AD group is added to SQL with necessary permission and when a particular user belongs to that group is not able to access SQL, you need to cross verify the AD group members list, so at that time you can utilize this procedure.

You need to use the below command to retrieve the AD group member details and the output will be similar to the one shown below. Please change correct AD group name before running the script. In this example Im going to retrieve the list of members existing in sqlarticlesSQLDBA AD group

EXEC XP_LOGININFO ‘sqlarticlesSQLDBA’, ‘members’

Sample Output