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))
EXEC sp_MSforeachdb 'select ''?'', name, sid from [?].sys.sysusers'
SELECT top 1000
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.