Click here to Skip to main content
15,610,839 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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'

What I have tried:

So far I have this query:

select name as username,
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.
Updated 28-Jan-21 20:17pm
Maciej Los 29-Jan-21 2:30am    
What database provider?

1 solution

this is for MS SQL-Server (you did not specify the database used)
here is the original source of the answer

DECLARE @vCommand nvarchar(4000) 
DECLARE @tResult TABLE (DBName varchar(255), UserName varchar(255))
SELECT @vCommand = 'use [?]
SELECT ''?'' AS DBName,
[name]       AS username
FROM sys.sysusers
WHERE islogin = 1
AND   hasdbaccess = 1'

EXEC sp_MSforeachdb @vCommand 

SELECT * FROM @tResult t 
WHERE t.UserName = 'user_1'
AND t.DBName like '%PRD%'
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900