The following query well known query will list all logins with SYSADMIN access to a sql Server:

    SELECT  p.name 'LoginName',
	    p.type_desc

    FROM    sys.server_principals p
    JOIN	sys.syslogins s ON p.sid = s.sid
	WHERE   
		p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
		-- Logins that are not process logins
        AND p.name NOT LIKE '##%'
		-- exclude disabled
		and p.is_disabled = 0
		-- Logins that are sysadmins
		AND s.sysadmin = 1
ORDER BY Type

The problem is that the query may return Windows Groups, and we might want to know membership to those groups.

Which is why we rather run the following query instaead:

/*
DESCRIPTION:  List users with SYSADMIN access 
If a Windows group has sysadmin access
it will list its members

Lists only enabled users at SQL level
cannot check if valid AD account

CONFIGURATION: None
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012
DOES NOT WORK

*/

DECLARE @logininfo table
( [LoginName] sysname
, [type] char(8), [priv] char(9), [mpln] sysname
, [GroupMembership] sysname
)

DECLARE @GroupName sysname

DECLARE cur CURSOR FOR

	SELECT
			p.name
    FROM    sys.server_principals p
    JOIN	sys.syslogins s ON p.sid = s.sid
	WHERE   
			p.type_desc IN ('WINDOWS_GROUP')
			-- exclude disabled
			and p.is_disabled = 0
			-- Logins that are sysadmins
			AND s.sysadmin = 1

OPEN cur
FETCH NEXT FROM cur INTO @GroupName
WHILE @@FETCH_STATUS = 0
BEGIN

	INSERT INTO @logininfo
	EXEC master..xp_logininfo 
	@acctname = @GroupName,
	@option = 'members'

FETCH NEXT FROM cur INTO @GroupName
END
CLOSE cur
DEALLOCATE cur

SELECT 
	LoginName,
	'WINDOWS_GROUP' 'Type_Desc',
	GroupMembership 'GroupMembership'
FROM @logininfo  
UNION ALL
SELECT  p.name 'LoginName',
		p.type_desc,
		'' 'GroupMembership'
    FROM    sys.server_principals p
    JOIN	sys.syslogins s ON p.sid = s.sid
	WHERE   
		p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN')
		-- Logins that are not process logins
        AND p.name NOT LIKE '##%'
		-- exclude disabled
		and p.is_disabled = 0
		-- Logins that are sysadmins
		AND s.sysadmin = 1
ORDER BY GroupMembership, Type_Desc