Audit SQL Logins - Updated

CREATE PROC usp_sql_audit
AS
/*

Audit SQL Server Users
--------------------------------------------------------------------------
Author:     Simon Facer
Date:     01/04/2007

This script will generate an audit of SQL Server logins, as well
as a listing of the database user ID's and the SQL Server login
that each DB user ID maps to.

In the database user ID results, [Server Login] = '** Orphaned **'
indicates that there is no matching Server login.

This script was originally designed for SQL 2000, but works just as 
well in SQL 2005.

--------------------------------------------------------------------------
Modifications
--------------------------------------------------------------------------
Modified by:     Derrick Holland
Date:                09/25/2007
Summary:            Script as stored procedure that can be executed from any
                    database; shortened column lengths for database name and
                    login name; changed order of columns for the database and
                    roles section.
--------------------------------------------------------------------------
*/
SET NOCOUNT ON

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
 DROP TABLE ##Users;
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
 DROP TABLE ##DBUsers;

-- ***************************************************************************
-- Always run this from master  --Not needed
-- USE master 
-- ***************************************************************************

-- ***************************************************************************
-- Declare local variables
DECLARE @DBName VARCHAR(32);
DECLARE @SQLCmd VARCHAR(1024);
-- ***************************************************************************

-- ***************************************************************************
-- Get the SQL Server logins
-- Create Temp User table
CREATE TABLE ##Users (
    [sid] varbinary(85) NULL,
    [Login Name] varchar(24) NULL,
    [Default Database] varchar(18) NULL,
    [Login Type] varchar(9),
    [AD Login Type] varchar(8),
    [sysadmin] varchar(3),
    [securityadmin] varchar(3),
    [serveradmin] varchar(3),
    [setupadmin] varchar(3),
    [processadmin] varchar(3),
    [diskadmin] varchar(3),
    [dbcreator] varchar(3),
    [bulkadmin] varchar(3));
---------------------------------------------------------
INSERT INTO ##Users 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]
FROM master.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 User ID] VARCHAR(24),
 [Server Login] VARCHAR(24),
 [Database Role] VARCHAR(24),
     [Database] VARCHAR(24));
-- ***************************************************************************
-- ***************************************************************************
-- Declare a cursor to loop through all the databases on the server
DECLARE csrDB CURSOR FOR 
 SELECT name
 FROM master..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 su.[name] AS [Database User ID], ' +
 ' COALESCE (u.[Login Name], ''** Orphaned **'') AS [Server Login], ' +
 ' COALESCE (sug.name, ''Public'') AS [Database Role],' + 
                                 '            ''' + @DBName + ''' AS [Database]' +
 ' 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 [Database User ID],[Database];
-- ***************************************************************************
-- ***************************************************************************
-- 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;

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
 DROP TABLE ##DBUsers;
-- ***************************************************************************
GO