Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Finding Unused Database Roles Expand / Collapse
Author
Message
Posted Thursday, July 05, 2012 9:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 7,072, Visits: 6,230
Comments posted to this topic are about the item Finding Unused Database Roles

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1325522
Posted Thursday, July 05, 2012 10:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:42 PM
Points: 20,473, Visits: 14,120
Thanks Brandie. I even like that you posted the script using dbcc timewarp. Notice the publish date is July 24, 2012.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1325535
Posted Thursday, July 05, 2012 10:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 7,072, Visits: 6,230
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.


Shhhhh. You're not supposed to tell anyone. @=)


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1325542
Posted Thursday, July 05, 2012 10:20 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:42 PM
Points: 20,473, Visits: 14,120
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.


Shhhhh. 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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1325545
Posted Tuesday, July 24, 2012 6:44 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:10 PM
Points: 137, Visits: 585
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
Post #1334422
Posted Tuesday, July 24, 2012 6:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 7,072, Visits: 6,230
Your Name Here (7/24/2012)
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


Ken, what conflicts? Could you give me more details (and error messages) please?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1334432
Posted Tuesday, July 24, 2012 7:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:10 PM
Points: 137, Visits: 585
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
Post #1334450
Posted Tuesday, July 24, 2012 7:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 7,072, Visits: 6,230
Excuse me a moment.

<hides mouth behind hand> ROTFLOL
</puts hand down>

That's okay. No harm done.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1334454
Posted Tuesday, July 24, 2012 7:26 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:10 PM
Points: 137, Visits: 585
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.
Post #1334459
Posted Tuesday, July 24, 2012 7:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 7,072, Visits: 6,230
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1334475
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse