SQL - Iterate through a list of databases to return single result for orphaned users

I'm using the below code to give me a list of orphaned users for a database which works great. I'm now trying to build this into something that will iterate through the list of databases and return the results in a single table.

Here is the code I am using to obtain the orphaned users:

select DB_NAME() AS [Current Database], u.uid, u.name, u.sid, rm.role_principal_id as 'Role ID', dp1.name as 'Role'
from sys.sysusers u
left join sys.syslogins l on UPPER(u.sid) = UPPER(l.sid)
inner join sys.database_role_members rm on rm.member_principal_id = u.uid
left join sys.database_principals dp on dp.principal_id = rm.member_principal_id
left join sys.database_principals dp1 on dp1.principal_id = rm.role_principal_id 
where u.uid > 4 and u.issqlrole = 0
--and issqluser = 1 --commented out to include orphaned windows logins
and l.name is null
order by 1

I can get a list of databases using the below but I want to combine with the above to go through each database in turn.

SELECT name FROM sys.databases 
WHERE database_id > 4

Any help or pointers with this would be much appreciated!

Thanks :-)

Solution

You can use the system stored procedure sp_msforeachdb to run a set of SQL statements against each database. Each execution of that procedure, in this case, will return a separate result set so we are using a temporary table to gather all the results up into a single table. This single table you then can manipulate using normal SQL to filter and sort your final results.

IF OBJECT_ID('tempdb..#Orphans') IS NOT NULL
  DROP TABLE #Orphans

CREATE TABLE #Orphans 
(
  [Current Database] SYSNAME,
  [uid]              SMALLINT,
  [name]             SYSNAME,
  [sid]              VARBINARY(85),
  [Role ID]          SMALLINT,
  [Role]             SYSNAME
)

INSERT #Orphans
EXEC sp_msforeachdb 'use [?];
    select DB_NAME() AS [Current Database]
         , u.uid, u.name
         , u.sid
         , rm.role_principal_id as [Role ID]
         , dp1.name as [Role]
      from sys.sysusers u
             left join sys.syslogins l 
               on UPPER(u.sid) = UPPER(l.sid)
                 inner join sys.database_role_members rm 
                   on rm.member_principal_id = u.uid
                     left join sys.database_principals dp 
                       on dp.principal_id = rm.member_principal_id
                         left join sys.database_principals dp1 
                           on dp1.principal_id = rm.role_principal_id 
     where u.uid > 4 and u.issqlrole = 0
     --and issqluser = 1 --commented out to include orphaned windows logins
       and l.name is null
    order by 1'

SELECT * FROM #Orphans

A few things to note is that the "?" character in the SQL template will be filled in with the database name each time the template is executed in a new database. Also I changed your column aliases to use the "[" characters instead of the single quotes.