IF  EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
    DROP TABLE ##Users
 
GO
IF  EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
    DROP TABLE ##DBUsers
 
GO
 
-- ***************************************************************************
-- Always run this from master
USE master 
-- ***************************************************************************
 
-- ***************************************************************************
-- Declare local variables
DECLARE @DBName             VARCHAR(32)
DECLARE @SQLCmd             VARCHAR(1024)
-- ***************************************************************************
 
-- ***************************************************************************
-- Get the SQL Server logins
SELECT  sid,
        loginname AS [Login Name], 
        dbname AS [Default Database],
        CASE isntname 
            WHEN 1 THEN 'AD Login'
            ELSE 'SQL Login'
        END AS [Login Type],
        CASE 
            WHEN isntgroup = 1 THEN 'AD Group'
            WHEN isntuser = 1 THEN 'AD User'
            ELSE ''
        END AS [AD Login Type],
        CASE sysadmin
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [sysadmin],
        CASE [securityadmin]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [securityadmin],
        CASE [serveradmin]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [serveradmin],
        CASE [setupadmin]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [setupadmin],
        CASE [processadmin]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [processadmin],
        CASE [diskadmin]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [diskadmin],
        CASE [dbcreator]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [dbcreator],
        CASE [bulkadmin]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [bulkadmin]
INTO ##Users
FROM dbo.syslogins
 
SELECT  [Login Name],   
        [Default Database],   
        [Login Type],   
        [AD Login Type],      
        [sysadmin],     
        [securityadmin],      
        [serveradmin],  
        [setupadmin],   
        [processadmin], 
        [diskadmin],    
        [dbcreator],    
        [bulkadmin]
FROM ##Users
ORDER BY [Login Type], [AD Login Type], [Login Name]
-- ***************************************************************************
 
-- ***************************************************************************
-- Create the output table for the Database User ID's
CREATE TABLE ##DBUsers (
    [Database]          VARCHAR(64),
    [Database User ID]  VARCHAR(64),
    [Server Login]      VARCHAR(64),
    [Database Role]     VARCHAR(64))
-- ***************************************************************************
 
-- ***************************************************************************
-- Declare a cursor to loop through all the databases on the server
DECLARE csrDB CURSOR FOR 
    SELECT name
        FROM sysdatabases
        WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
-- ***************************************************************************
 
-- ***************************************************************************
-- Open the cursor and get the first database name
OPEN csrDB
FETCH NEXT 
    FROM csrDB
    INTO @DBName
-- ***************************************************************************
 
-- ***************************************************************************
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
    BEGIN
-- ***************************************************************************
 
-- ***************************************************************************
-- 
        SELECT @SQLCmd = 'INSERT ##DBUsers ' +
                         '  SELECT ''' + @DBName + ''' AS [Database],' +
                         '       su.[name] AS [Database User ID], ' +
                         '       COALESCE (u.[Login Name], ''** Orphaned **'') AS [Server Login], ' +
                         '       COALESCE (sug.name, ''Public'') AS [Database Role] ' +
                         '    FROM [' + @DBName + '].[dbo].[sysusers] su' +
                         '        LEFT OUTER JOIN ##Users u' +
                         '            ON su.sid = u.sid' +
                         '        LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' +
                         '                             INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug  ' +

                         '                                 ON sm.groupuid = sug.uid)' +

                         '            ON su.uid = sm.memberuid ' +

                         '    WHERE su.hasdbaccess = 1' +

                         '      AND su.[name] != ''dbo'' '

 

        EXEC (@SQLCmd)

-- ***************************************************************************

 

-- ***************************************************************************
-- Get the next database name
        FETCH NEXT 
            FROM csrDB
            INTO @DBName
-- ***************************************************************************
 
-- ***************************************************************************
-- End of the cursor loop
    END
-- ***************************************************************************
 
-- ***************************************************************************
-- Close and deallocate the CURSOR
CLOSE csrDB
DEALLOCATE csrDB
-- ***************************************************************************
 
-- ***************************************************************************
-- Return the Database User data
SELECT * 
    FROM ##DBUsers
    ORDER BY [SERVER LOGIN], [DATABASE ROLE]
-- ***************************************************************************
 
GO
-- ***************************************************************************
-- Clean up - delete the Global temp tables
IF  EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
    DROP TABLE ##Users
 
GO
IF  EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
    DROP TABLE ##DBUsers
-- ***************************************************************************
 
GO

Audit user login and database roles

SQL