Finding Unused Database Roles

  • Comments posted to this topic are about the item Finding Unused Database Roles

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

  • 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. @=)

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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.:-D

    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, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • 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

  • 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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

  • Excuse me a moment.

    <hides mouth behind hand> ROTFLOL

    </puts hand down>

    That's okay. No harm done.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

  • 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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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