question

SQLLover21-0870 avatar image
0 Votes"
SQLLover21-0870 asked MelissaMa-msft commented

Query to list all the databases that have a specific user

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;

I need to combine it with the below query:

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

How can I combine both?
Any help is appreciated.


sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @SQLLover21-0870,

Welcome to Microsoft Q&A!

You could use the stored procedure sp_msforeachdb.

sp_msforeachdb: This is a very useful system stored procedure that will execute any SQL script you pass to for in each of the databases on your SQL Server instance. The stored procedure just loops through the databases, which is simple to write, but it saves you from having to do it yourself.

Step One:Create the table in the master database.

 Create Table master.dbo.userPermissionResults
 (
 DatabaseName varchar(100),
 username varchar(100),
 create_date date,
 modify_date date,
 type varchar(100),
 authentication_type  varchar(100)
 )

Step Two: Then simply add the insert statement to the query which uses sp_msforeachdb.

 EXECUTE sp_msforeachdb 'Insert Into master.dbo.userPermissionResults 
 select ''[?]'' as DatabaseName,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;'

Step Three: Search in this userPermissionResults table .

 select * from master.dbo.userPermissionResults 
 where  DatabaseName like '%PRD%'

Reference:SQL Server: query database user roles for all databases in server

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SQLLover21-0870,

Could you please provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Take a look at http://www.sommarskog.se/dynamic_sql.html#alldatabases There I present something you could use as a boilerplate for your script.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.