Quick SQL Instance and DB Access Collection

SQLUserPermissionsQry.sql

/* 	We collect the results daily and haven't been too concerned 
	about lost history if a particular instance fails occasionally */
	
DELETE FROM 
dbo.secPoll;

CREATE TABLE ##userQuery
(
	serverName VARCHAR(255)
  , databaseName VARCHAR(255)
  , databaseRoleName VARCHAR(255)
  , memberName VARCHAR(255)
  , loginType VARCHAR(50)
  , status VARCHAR(50)
);

DECLARE @theTime DATETIME2(0) = GETDATE();

/* 	dynamic SQL for collecting DB permissions for each instance using Aaron Bertrands sp_foreachdb
	but you'd be better off incorporating the new procedure: 
	https://sqlblog.org/2020/08/04/reliable-flexible-replacement-sp_msforeachdb */
	
EXEC master.dbo.sp_foreachdb @command = N'USE[?]; 

INSERT INTO ##userQuery (serverName
					   , databaseName
					   , databaseRoleName
					   , memberName
					   , loginType
					   , status)
					   SELECT	@@SERVERNAME AS serverName, DB_NAME() AS databaseName
	  , DP1.name AS DatabaseRoleName
	  , ISNULL(DP2.name, ''No members'') AS MemberName
	  , CASE DP2.type
			WHEN ''U'' THEN ''WinLogin''
			WHEN ''S'' THEN ''SQLLogin''
			WHEN ''G'' THEN ''WinGroupLogin''
			ELSE ''UNKNOWN''
		END AS LoginType
	 , CASE WHEN l.name IS NULL THEN ''Orphan'' ELSE ''N/A'' END AS [status]
FROM
	[?].sys.database_role_members AS DRM
	RIGHT OUTER JOIN [?].sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id
	LEFT OUTER JOIN [?].sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id
	LEFT OUTER JOIN master.sys.syslogins l ON l.sid = DP2.sid
WHERE
	DP1.type = ''R''
	AND DP2.name IS NOT NULL
	AND DP2.name <> ''dbo''
	AND DP2.type IN (''G'', ''U'', ''S'')'
						   , @exclude_list = N'tempdb,model'
						   , @print_dbname = 1;

/* 	union all the db permissions with the instance permissions
	and stuff it into the local table */
INSERT INTO dbo.secPoll (serverName
				   , databaseName
				   , databaseRoleName
				   , memberName
				   , loginType
				   , status
				   , dateDetected)
SELECT	serverName
	  , databaseName
	  , databaseRoleName
	  , memberName
	  , loginType
	  , status
	  , @theTime AS dateDetected
FROM	##userQuery
WHERE	memberName NOT LIKE '##%'
UNION
SELECT	@@SERVERNAME AS serverName
	  , 'N/A' AS databaseName
	  , CASE WHEN b.sysadmin = 1 THEN
				 'sysadmin'
			WHEN b.securityadmin = 1 THEN
				'securityadmin'
			WHEN b.serveradmin = 1 THEN
				'serveradmin'
			WHEN b.setupadmin = 1 THEN
				'setupadmin'
			WHEN b.processadmin = 1 THEN
				'processadmin'
			WHEN b.diskadmin = 1 THEN
				'diskadmin'
			WHEN b.dbcreator = 1 THEN
				'dbcreator'
			WHEN b.bulkadmin = 1 THEN
				'bulkadmin'
			ELSE
				'Public'
		END AS ServerRole
	  , a.name AS MemberName
	  , CASE WHEN a.type_desc = 'SQL_LOGIN' THEN
				 'SQLLogin'
			WHEN a.type_desc = 'WINDOWS_GROUP' THEN
				'WinGroupLogin'
			ELSE
				'WinLogin'
		END AS LoginType
	  , CASE WHEN a.is_disabled = 1 THEN
				 'Disabled'
			WHEN a.is_disabled = 0 THEN
				'Enabled'
			WHEN a.is_disabled IS NULL THEN
				'Error'
		END AS Status
		, @theTime AS dateDetected
FROM	sys.server_principals AS a
	JOIN master.dbo.syslogins AS b ON a.sid = b.sid
WHERE	a.name NOT LIKE '##%'
ORDER BY serverName
	   , memberName;

DROP TABLE ##userQuery;