TSQL Query to Find all database users mapped to db_owner role

 ----------------------------------Find all database users mapped to db_owner role-----------------------

use master

Exec sp_MSforeachdb 'SELECT ''?'' AS [Database Name], su1.name AS [Database User Name], su2.name AS [Database Role]

FROM [?].sys.database_role_members r

INNER JOIN [?]..sysusers su1 ON su1.[uid] = r.member_principal_id

INNER JOIN [?]..sysusers su2 ON su2.[uid] = r.role_principal_id

WHERE su2.name IN(''db_owner'') AND su1.name NOT IN(''dbo'')'

--------------------------Find particular database users mapped to db_owner role-------------------

USE master-----change db name here

GO

SELECT  members.name as 'members_name', roles.name as 'roles_name',roles.type_desc as 'roles_desc',members.type_desc as 'members_desc'

FROM sys.database_role_members rolemem

INNER JOIN sys.database_principals roles

ON rolemem.role_principal_id = roles.principal_id

INNER JOIN sys.database_principals members

ON rolemem.member_principal_id = members.principal_id

where roles.name = 'db_owner' and members.name<>'dbo'

ORDER BY members.name

-------------------------------------------------------------sp_MSforeachdb--------------------------------------

CREATE PROCEDURE dbo.sp_MSforeachdb    

@command1 NVARCHAR(2000),   

@replacechar nchar(1) = N'?',   

@command2 NVARCHAR(2000) = NULL,  

@command3 NVARCHAR(2000) = NULL,    

@precommand NVARCHAR(2000) = NULL,   

@postcommand NVARCHAR(2000) = NULL    

AS    

    SET deadlock_priority low    

        

 /* This proc RETURNs one or more rows for each accessible db, with each db defaulting to its own result SET */    

 /* @precommand and @postcommand may be used to force a single result SET via a temp table. */    

    

 /* Preprocessor won't replace within quotes so have to use STR(). */    

 DECLARE @inaccessible NVARCHAR(12), @invalidlogin NVARCHAR(12), @dbinaccessible NVARCHAR(12)    

 SELECT @inaccessible = LTRIM(STR(CONVERT(INT, 0x03e0), 11))    

 SELECT @invalidlogin = LTRIM(STR(CONVERT(INT, 0x40000000), 11))    

 SELECT @dbinaccessible = N'0x80000000'  /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in CONVERT() */    

    

 IF (@precommand IS NOT NULL)    

  EXEC(@precommand)    

    

 DECLARE @origdb NVARCHAR(128)    

 SELECT @origdb = DB_NAME()    

    

 /* IF it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */    

   /* CREATE the SELECT */    

 EXEC(N'DECLARE hCForEachDatabASe cursor global for SELECT name from mASter.dbo.sysdatabASes d ' +    

   N' where (d.status & ' + @inaccessible + N' = 0)' +    

   N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (hAS_dbaccess(d.name) = 1))' )    

    

 DECLARE @retval INT    

 SELECT @retval = @@error    

 IF (@retval = 0)    

  EXEC @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1    

    

 IF (@retval = 0 and @postcommand IS NOT NULL)    

  EXEC(@postcommand)    

    

   DECLARE @tempdb NVARCHAR(258)    

   SELECT @tempdb = REPLACE(@origdb, N']', N']]')    

   EXEC (N'use ' + N'[' + @tempdb + N']')    

    

 RETURN @retval  

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