0

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

1 Answer 1

-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,

4
  • 1
    The reason why != should be avoided is that is it not standard, though several databases do support it.
    – vonPryz
    Feb 1, 2021 at 12:12
  • This query runs in a single database. sys.database_principals only lists the users in the current database. Oct 7, 2023 at 22:55
  • @DavidBrowne-Microsoft thanks for pointing it out and you could have made a sugestion on how to fix it. I edited my answer.
    – Pimenta
    Oct 10, 2023 at 10:11
  • Check out @charlieface's comment for the correct approach. Oct 10, 2023 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.