Today's script will list all principals with membership in the db_owner fixed database role.
/*
-----------------------------------------------------------------
Database Owner Permissions
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
IF OBJECT_ID('tempdb..#tmp') IS NULL
CREATE TABLE #tmp
(
Principal VARCHAR(250),
DatabaseName VARCHAR(250)
);
GO
EXEC sp_Msforeachdb 'use [?];
INSERT #tmp SELECT u.name AS Principal, db_name() AS DatabaseName
FROM sys.database_role_members drm
INNER JOIN sys.database_principals dp ON dp.principal_id = drm.role_principal_id
INNER JOIN sysusers u ON u.uid = drm.member_principal_id
WHERE dp.name = ''db_owner''
AND dp.name <> ''dbo'' AND u.isntuser = 0';
GO
SELECT Principal,
DatabaseName
FROM #tmp
WHERE Principal != 'dbo';
GO
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp;
GO
Enjoy!
Chris
