SET NOCOUNT ON
/*
** "Challenger, go at throttle up..."
*/

-- Create variables
DECLARE @dbname      VARCHAR(128),
        @loopcounter INT,
        @sqlstmt     VARCHAR(MAX)

-- Create table variable for database names
DECLARE @dbnames TABLE (
   [RecID]           INT IDENTITY(1,1) PRIMARY KEY,
   [DBName]          VARCHAR(128)
   )

-- Create global temporary table for roles found
CREATE TABLE ##UnusedDatabaseRoles (
   [DBName]          VARCHAR(50), 
   [RoleName]        VARCHAR(50)
   )

-- Create global temporary table for roles to ignore
CREATE TABLE ##ExemptRoles (
   [Name]            VARCHAR(128) NOT NULL
   )

-- Populate database name table variable
INSERT INTO @dbnames ([DBName])
   SELECT [name]
      FROM master.sys.databases
      WHERE [name] NOT IN ('model', 'tempdb') 
      AND [source_database_id] IS NULL
      AND (
             DATABASEPROPERTY([name], 'IsEmergencyMode') = 0
         AND DATABASEPROPERTY([name], 'IsInLoad') = 0
         AND DATABASEPROPERTY([name], 'IsInRecovery') = 0
         AND DATABASEPROPERTY([name], 'IsInStandBy') = 0
         AND DATABASEPROPERTY([name], 'IsNotRecovered') = 0
         AND DATABASEPROPERTY([name], 'IsOffline') = 0
         AND DATABASEPROPERTY([name], 'IsShutDown') = 0
         AND DATABASEPROPERTY([name], 'IsSuspect') = 0
         AND [state] = 0
         )

-- Identify any roles to ignore
INSERT INTO ##ExemptRoles ([Name])
   VALUES ('db_dtsadmin'), ('db_dtsltduser'), ('db_dtsoperator')

-- Let's get this party started!
SET @loopcounter = (SELECT MIN([RecID]) FROM @dbnames)
WHILE @loopcounter <= (SELECT MAX([RecID]) FROM @dbnames)
BEGIN
   SELECT @dbname = [DBName] 
      FROM @dbnames
      WHERE [RecID] = @loopcounter
   SET @sqlstmt = 'USE [' 
                + @dbname + '];' 
                + ' INSERT INTO ##UnusedDatabaseRoles ([DBName], [RoleName])'
                + ' SELECT DISTINCT ' 
                + QUOTENAME(@dbname, CHAR(39)) 
                + ', RP.[name]'
                + ' FROM sys.database_principals rp' 
                + ' LEFT OUTER JOIN sys.database_role_members r'
                + ' ON rp.[principal_id] = r.[role_principal_id]'
                + ' WHERE rp.[type] = CHAR(82)'
                + ' AND r.[role_principal_id] IS NULL'
                + ' AND rp.[is_fixed_role] = 0'
                + ' AND rp.[principal_id] > 0'
                + ' AND rp.[Name] NOT IN (SELECT [Name]'
                --+ ' COLLATE SQL_Latin1_General_CP1_CI_AS '
                + ' FROM ##ExemptRoles)';
   --PRINT @sqlstmt
   EXECUTE (@sqlstmt);
   SET @loopcounter = @loopcounter + 1;
END

-- Show results
SELECT * FROM ##UnusedDatabaseRoles

/*
** Housekeeping
*/
DROP TABLE ##UnusedDatabaseRoles
DROP TABLE ##ExemptRoles
SET NOCOUNT OFF
GO