July 5, 2012 at 9:46 am
Comments posted to this topic are about the item Finding Unused Database Roles
July 5, 2012 at 10:06 am
Thanks Brandie. I even like that you posted the script using dbcc timewarp. Notice the publish date is July 24, 2012.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 5, 2012 at 10:17 am
SQLRNNR (7/5/2012)
Thanks Brandie. I even like that you posted the script using dbcc timewarp. Notice the publish date is July 24, 2012.:-D
Shhhhh. You're not supposed to tell anyone. @=)
July 5, 2012 at 10:20 am
Brandie Tarvin (7/5/2012)
SQLRNNR (7/5/2012)
Thanks Brandie. I even like that you posted the script using dbcc timewarp. Notice the publish date is July 24, 2012.:-DShhhhh. You're not supposed to tell anyone. @=)
K - I'll keep it hush hush
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 24, 2012 at 6:44 am
Brandie
Thanks for the script - I'm sure it'll come in handy. One FYI though: the dynamic SQL didn't handle collation conflicts (doggone that ReportServer db... <grin>).
Cheers,
Ken
July 24, 2012 at 6:59 am
Your Name Here (7/24/2012)
BrandieThanks for the script - I'm sure it'll come in handy. One FYI though: the dynamic SQL didn't handle collation conflicts (doggone that ReportServer db... <grin>).
Cheers,
Ken
Ken, what conflicts? Could you give me more details (and error messages) please?
July 24, 2012 at 7:17 am
Brandie
I apologize - I'd run the script as a script rather than a stored procedure (some servers I manage don't have a utility db to create procedures in) and it errored on the ReportServer and ReportServerTempDb collation. Running it as a procedure on a server that I [could] create the procedure on ran just fine.
Sorry for the false alarm.
Ken
July 24, 2012 at 7:20 am
Excuse me a moment.
<hides mouth behind hand> ROTFLOL
</puts hand down>
That's okay. No harm done.
July 24, 2012 at 7:26 am
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.
July 24, 2012 at 7:41 am
The interesting thing is that I have those collations and I have the Report dbs on my production server and my script works just fine.
I always develop my scripts in SSMS without the stored procedure creation part to make sure they work and I never hit that error, so I don't know what's different between our servers.
July 24, 2012 at 7:54 am
Yeah, I do the same thing. I found it odd as well because I use dynamic SQL to traverse a database list regularly. It caught me by surprise so I thought I'd pass it back to you in case others may have run into the same thing.
It's not a big deal and easy to fix. Honestly I figured I'd torqued it up when I did the sp-to-script conversion... <grin>
Anyway, thanks for the script. I appreciate it and will definitely use it.
Cheers,
Ken
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply