How to get information about Windows users and groups using T-SQL
Every now and then, someone will ask in the SQLTeam forums how to find out what Windows users are members of a particular Windows group so that they can find out who has access to their SQL Server. The question doesn't get asked often, but I can never remember what the answer is unless I google it. Now I'll be able to find the answer here.
SQL Server provides an extended stored procedure, xp_logininfo, to get information about Windows users and groups.
Syntax:
xp_logininfo [ [ @acctname = ] 'account_name' ]
[ , [ @option = ] 'all' | 'members' ]
[ , [ @privilege = ] variable_name OUTPUT]
xp_logininfo enables us to find out what Windows users are members of a particular Windows group. For instance: EXEC master.dbo.xp_logininfo 'DomainName\GroupName', 'members'
xp_logininfo also enables us to find out what Windows groups a particular Windows user is a member of. For instance: EXEC master.dbo.xp_logininfo 'DomainName\UserName'
@acctname must be full qualified. If your Windows user or Windows group is local rather than in Active Directory, simply use WorkgroupName instead of DomainName.
If the Windows user or Windows group exists but does not have access to the SQL Server instance, you will get an empty result set returned. It will not error.