SQL Server : auditing AD Users - how can we identity AD User account from AD Groups I would like to identity which accounts on our servers belong to AD Users.

Currently, I find a plethora of accounts from users that should in fact be part of AD Groups registered on the same SQL Server. In effect these AD User accounts are redundant (or they should be).

Question is: how can we identify these AD users and AD groups in SQL Server?

Thanks,
Below is a script that uses xp_login_info (https://msdn.microsoft.com/en-us/library/ms190369.aspx) to get the AD permission paths for each AD user and group with a SQL Server login.

SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#results', 'U') IS NOT NULL
     DROP TABLE #results;
CREATE TABLE #results(
      account_name sysname
    , type char(8)
    , privilege char(9)
    , mapped_user_name sysname
    , permission_path sysname NULL
    );

DECLARE @SQL nvarchar(MAX) = (
    SELECT 'INSERT INTO #results EXECUTE xp_logininfo ''' + name + ''', ''all'';'
    FROM sys.server_principals
    WHERE type_desc IN(N'WINDOWS_LOGIN', N'WINDOWS_GROUP')
    FOR XML PATH(''));

EXECUTE(@SQL);

SELECT
      account_name
    , type
    , privilege
    , mapped_user_name
    , permission_path
FROM #results
ORDER BY account_name, mapped_user_name;