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%'

• Charlieface: You may find this useful stackoverflow.com/questions/65593774/… You need to build and run a dynamic query based on all databases, and union the result together

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,

• vonPryz: The reason why != should be avoided is that is it not standard, though several databases do support it.
• David Browne - Microsoft: This query runs in a single database. sys.database_principals only lists the users in the current database.
• Pimenta: @DavidBrowne-Microsoft thanks for pointing it out and you could have made a sugestion on how to fix it. I edited my answer.
• David Browne - Microsoft: Check out @charlieface's comment for the correct approach.