I saw someone that wanted to get all users from all databases on their instance. Seems like that ought to be simple, right?

The user wanted to use sp_MSforeachdb to query users, but wanted a single result set for all databases.  Why you need this, I’m not sure. I guess some auditing report. Or maybe looking to clean up and remove unnecessary users?

In any case, this turned out to be easy with a caveat. It’s not in one single statement..

DECLARE @MyUsers TABLE ( dbname VARCHAR(200), principalname VARCHAR(200), principalSID VARBINARY(MAX))
  INSERT @MyUsers

EXEC sp_MSforeachdb 'select ''?'', name, sid from [?].sys.sysusers'
SELECT top 1000
  *
  FROM @myusers

Use the ? in the query to get the users from every db. Then store this in a table that you can pull back all the data.

This worked for me, though since I have some database names with a hypen (-) in them, I needed the brackets to get this to work.