Hi @SQLLover21 ,
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%'
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.