-- CREATING A TEMP TABLE TO LOAD THE DATABASE USERS WITH DATABASE ROLES ; 
CREATE TABLE [TEMPDB].[DBO].[DB_ROLES]([DBNAME] [SYSNAME] ,[USERNAME] [SYSNAME] ,[DB_OWNER] [VARCHAR](3) ,[DB_ACCESSADMIN] [VARCHAR](3) ,[DB_SECURITYADMIN] [VARCHAR](3) , [DB_DDLADMIN] [VARCHAR](3) ,[DB_DATAREADER] [VARCHAR](3) ,[DB_DATAWRITER] [VARCHAR](3) ,[DB_DENYDATAREADER] [VARCHAR](3) ,[DB_DENYDATAWRITER] [VARCHAR](3) ,[DT_CREATE] [DATETIME] NOT NULL,[DT_UPDATE] [DATETIME] NOT NULL,[DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3]  DEFAULT (GETDATE()) ) ON [PRIMARY] 
GO 
INSERT INTO    [TEMPDB].[DBO].[DB_ROLES] EXEC SP_MSFOREACHDB '     SELECT      ''?'' AS DBNAME,       USERNAME,      MAX(CASE ROLENAME WHEN ''DB_OWNER''         THEN ''YES'' ELSE ''NO'' END) AS DB_OWNER,        MAX(CASE ROLENAME WHEN ''DB_ACCESSADMIN ''   THEN ''YES'' ELSE ''NO'' END) AS DB_ACCESSADMIN ,        MAX(CASE ROLENAME WHEN ''DB_SECURITYADMIN''  THEN ''YES'' ELSE ''NO'' END) AS DB_SECURITYADMIN,        MAX(CASE ROLENAME WHEN ''DB_DDLADMIN''   THEN ''YES'' ELSE ''NO'' END) AS DB_DDLADMIN,        MAX(CASE ROLENAME WHEN ''DB_DATAREADER''        THEN ''YES'' ELSE ''NO'' END) AS DB_DATAREADER,        MAX(CASE ROLENAME WHEN ''DB_DATAWRITER''        THEN ''YES'' ELSE ''NO'' END) AS DB_DATAWRITER,      MAX(CASE ROLENAME WHEN ''DB_DENYDATAREADER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAREADER,        MAX(CASE ROLENAME WHEN ''DB_DENYDATAWRITER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAWRITER,        CREATEDATE,      UPDATEDATE,        GETDATE()        FROM (        SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME, B.CREATEDATE, B.UPDATEDATE             FROM                        .DBO.SYSMEMBERS A   JOIN .DBO.SYSUSERS  B  ON A.MEMBERUID = B.UID                         JOIN .DBO.SYSUSERS C ON A.GROUPUID = C.UID                          )S                   GROUP BY USERNAME, CREATEDATE, UPDATEDATE          ORDER BY USERNAME'

select a.*, b.is_disabled from [TEMPDB].[DBO].[DB_ROLES] a
LEFT JOIN sys.server_principals b
ON a.USERNAME = b.name
WHERE DBNAME IN ('DB1Main', 'DB2', 'DBBilling') AND (DB_OWNER = 'YES' OR DB_ACCESSADMIN = 'YES' OR DB_SECURITYADMIN = 'YES'
OR DB_DDLADMIN = 'YES' OR db_datareader = 'YES' OR db_datawriter = 'YES' OR DB_DENYDATAREADER = 'YES' OR DB_DENYDATAWRITER = 'YES') AND a.USERNAME <> 'dbo'