Query to list all the databases that have a specific user

SQLLover21 201 Reputation points
2021-01-28T20:37:19.107+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,486 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-01-29T05:40:23.993+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-01-28T22:47:36.283+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments