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 don’t 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 DBA’s will be limited to the permission so s\he 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 I’m going to retrieve the list of members existing in sqlarticles\SQLDBA AD group
EXEC XP_LOGININFO ‘sqlarticles\SQLDBA’, ‘members’
Sample Output
hello Vidhya,
I can see that you’re really explanatory but there are people reading your blogs everyday who are the first timers as far as SQL is concerned. So could you please abbreviate any new terms like for example, “AD” used in the above article.
I googled it to find it means Active Directory. If wrong, please crct me.
Thanks in advance,
Mahe
@Mahe – You are correct.. Sure I’ll elaborate in my future articles. Thanks for your concern
Hi Vidhya,
It will easily provide info,To find the who all the members of DBA Group without taking help from Winops Team.
Hi Vidya very informative blog and XP_loginfo has been particularly been helpful and interesting. I dont have go back to the systems guys everytime for this information.