USE master
GO
--get a list of SERVER users, and tell if they're SYSADMIN
SELECT p.name AS [loginname] ,
p.type ,
p.type_desc ,
p.is_disabled,
s.sysadmin ,
CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
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 '##%'
GO
--for each DATABASE (returns multiple result sets)
--get the name of each user in the db_owner role
exec sp_msForEachDb ' use [?]
select db_name() as [database_name], r.[name] as [role], p.[name] as [member] from
sys.database_role_members m
join
sys.database_principals r on m.role_principal_id = r.principal_id
join
sys.database_principals p on m.member_principal_id = p.principal_id
where
r.name = ''db_owner'''