Query to list all the databases that have a specific user

Question

I need to form a query that lists all the databases that include the words 'PRD' that have a single specific user name 'user_1'

So far I have this query:

select name as username,
create_date,
modify_date,
type_desc as type,
authentication_type_desc as authentication_type
from sys.database_principals
where type not in ('A', 'G', 'R', 'X')
and sid is not null
and name != 'guest'
and name = 'user_1'
order by username;

The above query will require me to do "use database name" for each database. How can I include all the databases that contain the word 'PRD' in it?

Any help is appreciated.

EDIT: How can I combine the above query with the query below?:

SELECT name FROM master.sys.databases
where name like '%PRD%'

Answer 1

(Run this query over Master database)

By using this inner join and the link is database_id = principal_id

select 
dp.[name] as username,
dp.create_date,
dp.modify_date,
dp.[type_desc] as [type],
dp.authentication_type_desc as authentication_type,
dp.principal_id,
m.[name]
FROM master.sys.databases m
LEFT JOIN sys.database_principals dp ON m.database_id = dp.principal_id
    and dp.[type] not in ('A', 'G', 'R', 'X')
    and dp.[sid] is not null
    and dp.[name] <> 'guest'
    and dp.[name] like 'user_1'
WHERE m.name like '%PRD%'
ORDER BY username;

Also try to avoid != and replace it by <>.

Hope it helps,