Who has system administrator (SA) rights ?

In script below, we are going to determine logins with SQL Server system administrator (SA) rights : 

-------------------------------------------------------------------------------------------------------------------

SELECT SP1.[name] AS Login,
SP2.[name] AS Permission
FROM sys.server_principals SP1 
JOIN sys.server_role_members SRM ON SP1.principal_id=SRM.member_principal_id
JOIN sys.server_principals SP2 ON SRM.role_principal_id = SP2.principal_id WHERE SP2.[name] = 'sysadmin' ORDER BY SP1.[name]

In script below, we are going to determine windows group users login with SQL Server system administrator (SA) rights :

-------------------------------------------------------------------------------------------------------------------

DECLARE @LoginName sysname
DECLARE @sql NVARCHAR (2000)
CREATE TABLE ##tmp_xp_logininfo
(AccountName varchar(128) NOT NULL,
Type varchar(10) NOT NULL,
Privilege varchar(10) NOT NULL,
MappedLoginName varchar(128) NOT NULL,
PermissionPath varchar(128) NOT NULL)
DECLARE cur_Loginfetch CURSOR FOR
SELECT [name]
FROM master.sys.server_principals
WHERE TYPE = 'G'
AND Name NOT IN ('NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT')
OPEN cur_Loginfetch
FETCH NEXT FROM cur_Loginfetch INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ##tmp_xp_logininfo
EXEC xp_logininfo @LoginName , 'members'
FETCH NEXT FROM cur_Loginfetch INTO @LoginName
END
CLOSE cur_Loginfetch
DEALLOCATE cur_Loginfetch
SELECT DISTINCT(LEFT(AccountName, 35)) AS LoginName,
LEFT(PermissionPath, 35) AS GroupName
FROM ##tmp_xp_logininfo
WHERE Privilege = 'admin'
ORDER BY 1

DROP TABLE ##tmp_xp_logininfo

Who has database owner (DBO) rights?

To find out which db users have database owner (DBO) rights, we can execute the following code against each database.

EXEC sp_helprolemember 'db_owner';