Find all databases where particular user exists and its role

Question

I have a huge instance containing 1000+ databases. I need to find a way to query entire instance and find databases that contain particular user and what role this user has. I am not interested whether the user is orphanded. I just want to know which databases have this user and which do not.

Lets say that my user is called TestUser. Databases that do not contain this user should return NULL.

I would like the results in the following format:

Column1 - Database Name

Column2 - UserName (if exists or else NULL)

Column3 - UserRole (if exists or else NULL)

Answer 1

Under the assumption that you are not looking for issuing 1000+ selects, one (extremely ugly) solution would be:

SELECT 'DB_1' , UserName  , UserRole
  FROM DB_1.UsersTable
 WHERE Username = 'TestUser'

UNION

SELECT 'DB_2' , UserName  , UserRole
  FROM DB_2.UsersTable
 WHERE Username = 'TestUser'

:
:

Another solution is to use DYNAMIC SQL:

1. Collect the list of all the DBs that you to check,
2. Build a string hosting a select statement like the one above,
3. Execute the statement.

Again, both methods are shameful.

Answer 2

create table #temp
(
dbname sysname,
dbrole sysname,
dbuser sysname
)

Exec sp_msforeachdb '
if db_id()>4
Begin

insert into #temp
select db_name(), rp.name as database_role, mp.name as database_user
from sys.database_role_members drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)

End
'

The Roles Part is referenced from here:
Get list of all database users with specified role