Quick view of User database and server roles

  • whenriksen

    SSCarpal Tunnel

    Points: 4742

    Comments posted to this topic are about the item Quick view of User database and server roles

    Wes
    (A solid design is always preferable to a creative workaround)

  • aleksey donskoy

    Mr or Mrs. 500

    Points: 565

    Interesting script.

    2 things first:

    1. If you have databases with different collation from your master database the script errors out on the name join. Amended as following:

    EXEC ( 'USE [' + @name + '];

    SELECT DB = DB_NAME(), RDP.name, MDP.name, SL.sysadmin, SL.securityadmin,

    SL.serveradmin, SL.processadmin, SL.setupadmin, SL.bulkadmin, SL.diskadmin, SL.dbcreator

    FROM sys.database_role_members AS DRM

    INNER JOIN sys.database_principals AS RDP

    ON DRM.role_principal_id = RDP.principal_id

    INNER JOIN sys.database_principals AS MDP

    ON DRM.member_principal_id = MDP.principal_id

    INNER JOIN master.dbo.syslogins AS SL

    ON MDP.name = SL.name COLLATE DATABASE_DEFAULT'

    2. Database names, user names ... are not good as VARCHAR(50). Sript trucates names.

    Beginning amended from:

    USE master

    /*Filters */

    DECLARE @DBName VARCHAR(50) = NULL; --exact match

    DECLARE @DBPrincipal VARCHAR(100) = NULL; --wildcard search

    /*Script*/

    DECLARE @name VARCHAR(50)

    DECLARE @T TABLE

    ( DB VARCHAR(50)

    , RolePrincipal VARCHAR(50)

    , DBPrincipal VARCHAR(50)...

    amanded as:

    USE master

    /*Filters */

    DECLARE @DBName SYSNAME = NULL; --exact match

    DECLARE @DBPrincipal SYSNAME = NULL; --wildcard search

    /*Script*/

    DECLARE @name SYSNAME

    DECLARE @T TABLE

    ( DB SYSNAME

    , RolePrincipal SYSNAME

    , DBPrincipal SYSNAME ...

    Then it runs fine.

    Will attempt to make an SSMS custom report.

    Thanks

    Alex Donskoy

    Greenberg Trauriq PA

    Miami, FL

  • aleksey donskoy

    Mr or Mrs. 500

    Points: 565

    See the custom SSMS report attached.

    Works in SSMS 2005, 2008, 2008R2.

    Added interactive sorting on all report colums so that you could se it by database name, by principal name, then examine all system admins, all data readers , and so on...

    The file is zipped as the forum is not allowing .rdl uploads

    Enjoy, and thanks for the scipt again. Very helpful to curious people.

    Let me know if report having any issues.

    Alex Donskoy

  • whenriksen

    SSCarpal Tunnel

    Points: 4742

    Great feedback.

    I hadn't run into the collation issues.

    I haven't run into any truncation issues, but I know that our database names and usernames are limited in length. Your sysname approach is a good change also.

    Thanks again for the improvements.

    Wes
    (A solid design is always preferable to a creative workaround)

  • SQL_Hunt

    SSC-Dedicated

    Points: 33267

    One more thing...

    If there is any login account but if that does not have any permission associated to it, the report does not pull out the report. It would have been better if the script can pull out all the login accounts and if any specific account does not have any priovilege, the entire row should stand empty.

    Thanks.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply