USE master 
GO 


DECLARE @dbname SYSNAME 


SET NOCOUNT ON 


CREATE TABLE #logins 
( 
dbname SYSNAME NOT NULL, 
sid VARBINARY(85) NOT NULL, 
name SYSNAME NOT NULL, 
isdbo SMALLINT NOT NULL 
) 


DECLARE dbcsr INSENSITIVE CURSOR FOR 
SELECT name FROM dbo.sysdatabases 
WHERE status & 33760 = 0 
FOR READ ONLY 


OPEN dbcsr 


FETCH NEXT FROM dbcsr INTO @dbname 


WHILE @@FETCH_STATUS = 0 
BEGIN 


EXEC ('USE ' + @dbname + ' 


DECLARE @dborole SMALLINT 


SELECT @dborole = uid 
FROM dbo.sysusers 
WHERE name = N''db_owner'' AND status = 0 


INSERT INTO #logins (dbname, sid, name, isdbo) 
SELECT DB_NAME(), sid, name, ISNULL(groupuid, 0) 
FROM sysusers AS su LEFT JOIN (SELECT memberuid, groupuid FROM sysmembers 
WHERE groupuid = @dborole) AS sm 
ON su.uid = sm.memberuid 
WHERE sid IS NOT NULL') 


FETCH NEXT FROM dbcsr INTO @dbname 


END 


CLOSE dbcsr 


DEALLOCATE dbcsr 


SET NOCOUNT OFF 


SELECT sl.loginname, 
ISNULL(l.dbname, N'<None>') as dbname, 
ISNULL(l.name, N'<None>') AS name, 
CASE l.isdbo WHEN 0 THEN 'No' ELSE 'Yes' END AS dbo 
FROM master.dbo.syslogins AS sl LEFT JOIN #logins AS l 
ON sl.sid = l.sid 
WHERE IS_SRVROLEMEMBER ( N'sysadmin', sl.loginname) = 0 
UNION ALL 
SELECT loginname, N'<All>', N'<N/A>', N'Yes' 
FROM master.dbo.syslogins 
WHERE IS_SRVROLEMEMBER ( N'sysadmin', loginname) = 1 
ORDER BY sl.loginname, l.dbname 


DROP TABLE #logins