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
0

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 sys.database_principals dp
INNER JOIN master.sys.databases m ON m.database_id = dp.principal_id
    AND m.name like '%PRD%'
where dp.[type] not in ('A', 'G', 'R', 'X')
and dp.[sid] is not null
and dp.[name] <> 'guest'
and dp.[name] = 'user_1'
order by username;

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

Hope it helps,

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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