List database users and associated server logins with rights

Problem

Sometimes we are facing a Microsoft SQL Server Instance with tens of databases, Server Logins and lots of Users per Database. You do not really have the time and are willing to click through all the Logins and click Login -> Server Roles or double click each Server Role to see which Login is Member of this very Server Role.

Solution

So i wrote a script that exactly gives me a quick overview. Executing it for each Database using the undocumented feature master.dbo.sp_MSforeachdb i cycle through [DatabaseName].sys.sysusers, master.sys.syslogins, sys.database_principals and sys.database_role_members.

First i create a Temporary Table with all the Columns that i’d like to see:

  • DatabaseName, UserName and LoginName
  • Database Role Membership
  • Server Roles

    Then i insert via EXEC master.dbo.sp_MSforeachdb the desired rows into the Temporary Table. After that i Select from that Temporary Table and exclude all rows that contain infos regarding the four System Databases. The Server Login ‘sa’ is also not of much interest as it’s obvious what he’s got for rights.

    So here’s the complete script:

    
    /*
    	@cms4j, 08.05.2014
    	Script to show users in a database together with their associated login
    	and the rights within the databas and server roles.
    */
    
    CREATE TABLE #DB2User2Login
    (
    	DatabaseName NVARCHAR(255)
    	, UserName NVARCHAR(255)
    	, LoginName NVARCHAR(255)
    	, DatabaseRoleName NVARCHAR(255)
    	, ServerIsNTUser BIT
    	, ServerSysAdmin BIT
    	, ServerSecurityAdmin BIT
    	, ServerServerAdmin BIT
    	, ServerSetupAdmin BIT
    	, ServerProcessAdmin BIT
    	, ServerDiskAdmin BIT
    	, ServerDBCreator BIT
    	, ServerBulkAdmin BIT
    )
    
    EXEC dbo.sp_MSforeachdb 'USE [?];
    INSERT  INTO #DB2User2Login
            ( DatabaseName ,
              UserName ,
              LoginName ,
              DatabaseRoleName ,
              ServerIsNTUser ,
              ServerSysAdmin ,
              ServerSecurityAdmin ,
              ServerServerAdmin ,
              ServerSetupAdmin ,
              ServerProcessAdmin ,
              ServerDiskAdmin ,
              ServerDBCreator ,
              ServerBulkAdmin
            )
            SELECT  DB_NAME() ,
                    u.name ,
                    l.name ,
                    p.name ,
                    l.isntuser ,
                    l.sysadmin ,
                    l.securityadmin ,
                    l.serveradmin ,
                    l.setupadmin ,
                    l.processadmin ,
                    l.diskadmin ,
                    l.dbcreator ,
                    l.bulkadmin
            FROM    sys.sysusers u
                    INNER JOIN master.sys.syslogins l ON l.sid = u.sid
                    LEFT OUTER JOIN sys.database_principals m ON m.sid = u.sid
                    LEFT OUTER JOIN sys.database_role_members rm ON rm.member_principal_id = m.principal_id
                    LEFT OUTER JOIN sys.database_principals p ON p.principal_id = rm.role_principal_id'
    
    SELECT		*
    FROM		#DB2User2Login
    WHERE		DatabaseName NOT IN ('master', 'model', 'msdb', 'tempdb')
    AND			LoginName <> 'sa'
    
    UNION	-- just to get all other SQL Server Logins that are not mapped to a Database
    
    SELECT		'_n/a' AS DatabaseName
    			, 'n/a' AS OwnerName
    			, 'n/a' AS UserName
    			, ol.loginname 
    			, 'n/a' AS DatabaseRoleName
    			,ol.isntuser, ol.sysadmin, ol.securityadmin, ol.serveradmin, ol.setupadmin, ol.processadmin, ol.diskadmin, ol.dbcreator, ol.bulkadmin
    			
    FROM		syslogins ol	-- as OrphanedLogins
    WHERE		ol.loginname <> 'sa'
    AND			ol.loginname NOT LIKE '##MS%'
    AND			ol.loginname NOT LIKE 'NT Service%'
    AND			ol.loginname NOT LIKE 'NT Authority%'
    ORDER BY	DatabaseName
    
    DROP TABLE #DB2User2Login
    
    

    I hope this script helps you to get a quick overview who’s acting on your SQL Server Instance.

    Thank you for reading.

    @cms4j

    Advertisements
  • This entry was posted in SQL Server and tagged . Bookmark the permalink.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s