Thanks for taking that so gracefully. Here's the script and the errors, one for ReportServer and one for ReportServerTempDB.

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

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.