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