TSQL: Audit user roles for all databases

A while ago Thomas LaRock (blog

twitter) posted a script that used sysusers and the sp_helpusers proc to audit user groups setup in your database. The original post is here. I’m busy documenting my environment and thought this would be a great addition to the info I collect.

The only issue I had with this script is that it would only audit users in the current database context. I want to automate this collection for all databases… so here’s an updated script that does precisely that;

IF OBJECT_ID('tempdb..#user_table') IS NOT NULL
BEGIN
	DROP TABLE #user_table;
END;

-- tmp Table to hold the user data
CREATE TABLE #user_table
(
	ServerName NVARCHAR(100) NULL,
	[Database] NVARCHAR(256) NULL,
	UserName NVARCHAR(128) NOT NULL,
	GroupName NVARCHAR(128) NULL,
	LoginName NVARCHAR(128) NULL,
	DefDBName NVARCHAR(256) NULL,
	DefSchemaName NVARCHAR(100) NULL,
	UserID INTEGER NOT NULL,
	[SID] UNIQUEIDENTIFIER NULL
);

DECLARE @sql NVARCHAR(MAX);
SET @sql = '

	DECLARE @name SYSNAME,
			@sql_string NVARCHAR(MAX);

	-- Cursor containing all users for the current database context
	DECLARE usr_name CURSOR READ_ONLY FOR SELECT [name]
										  FROM sysusers
										  WHERE hasdbaccess = 1
										  AND [name] NOT LIKE ''#%''
										  AND [name] NOT IN (''guest'');

	OPEN usr_name;
	FETCH NEXT FROM usr_name INTO @name;

	WHILE (@@FETCH_STATUS = 0) -- This loop processes each database
	BEGIN

		-- if it''s a windows login surround with square brackets
		IF (@name LIKE ''%\%'')
		BEGIN
			SET @name = ''['' + @name + '']'';
		END

		SET @sql_string = N''EXEC sp_helpuser '' + @name;

		INSERT INTO #user_table
		(
			UserName,
			GroupName,
			LoginName,
			DefDBName,
			DefSchemaName,
			UserId,
			[SID]
		)
		EXEC(@sql_string);

		-- Add Server & database name to dataset
		UPDATE #user_table
		SET ServerName = @@SERVERNAME,
		[Database] = DB_NAME()
		WHERE ServerName IS NULL
		AND [Database] IS NULL;

		-- Get the next database user
		FETCH NEXT FROM usr_name INTO @name; -- Get next user

	END

	-- Clean up
	CLOSE usr_name;
	DEALLOCATE usr_name;';

-- Add USE database statement to change db context
SET @sql = 'USE ?; ' + @sql;
-- Execute the string for each database
EXEC sp_MSforeachDB @sql;

SELECT *
FROM #user_table
ORDER BY LoginName, [Database];

This will return a database similar to below.