A user called me up a couple weeks back wanting to know why their co-worker was unable to successfully import and query data in SQL Server. I looked at the database server and the database in question to see what the users had been granted access to. However, I did not find either of the users explicitly granted permissions. I did find that there were a couple of Active Directory Groups with permissions.
I looked at the permissions of both groups and found that one of the groups had more permissions than the other. I needed to see if the user that called me and their co-worker were in one or both of those groups.
I have access to our Active Directory so I was able to check out the groups directly. However I may not always be that fortunate and wanted to know how I could get this information without having AD access and without asking the Windows admin directly.
I did some searches and found references to setting up a linked server pointing to Active Directory and/or using the ADSI (Active Directory Service Interface). I also located a powershell script that promised to return the members of AD Groups. These options are interesting – and are something I may try in the future – but at the moment I was looking for a way to find this information directly using TSQL.
Searching a little bit I found the SQL Server Server extended stored procedure “xp_logininfo”. See the Microsoft Technet Reference for xp_logininfo here.
I started by using each user’s account to see what groups, if any, they were a member of.
EXECUTE xp_logininfo 'CORP\UserWhoCalled', 'all'
EXECUTE xp_logininfo 'CORP\TheirCoworker', 'all'
Interesting – the coworker was not a member of the “CORP\DataImportGroupUsersGrp_sec” AD Group. Adding them to this group gave them the ability to import and query data as expected.
Going another step further I wanted to see if I could use the AD Groups with the xp_logininfo stored procedure to see other members of the groups.
EXECUTE xp_logininfo 'CORP\ApplicationUserGroup_sec', 'members'
This returned a large group (100+) accounts that were granted access to the database and application through this AD Group.
EXECUTE xp_logininfo 'CORP\DataImportGroupUsersGrp_sec', 'members'
This returned less accounts – but included both the user who called me and their co-worker.
*According to the Technet Reference the xp_logininfo procedure should only return information from AD Global Groups and not Universal Groups. This was not my experience. I received information about both groups even though the “CORP\ApplicationUserGroup_sec” group is global and the “CORP\DataImportGroupUsersGrp_sec” group is universal.