Product:
Microsoft SQL Server

Issue:

How list users in a database?

Suggestion:

To get all users in one database;

use [database_name]
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'
order by username;

To get all users on the SQL server;

declare @db varchar(100)
declare @user varchar(100)
declare c cursor for select name from sys.sysdatabases        

open c

fetch next from c into @db

while @@fetch_status = 0
begin
    print @db   
    exec ('use ' + @db)

    declare u cursor for select name from sys.sysusers
        where issqlrole <> 1 and hasdbaccess <> 0 and isntname <> 1

    open u   

    fetch next from u into @user

    while @@fetch_status = 0
    begin
        print @user
        fetch next from u into @user
    end

    print '--------------------------------------------------'
    close u     
    deallocate u    
    fetch next from c into @db
end

close c
deallocate c

 

To list the DB_OWNERS in database, use this code:

SELECT user_name(member_principal_id)

FROM   sys.database_role_members

WHERE  user_name(role_principal_id) = 'db_owner'

To get more information, you can query like this:

SELECT  members.name MemberName, roles.name,roles.type_desc,members.type_desc

FROM sys.database_role_members rolemem

INNER JOIN sys.database_principals roles

    ON rolemem.role_principal_id = roles.principal_id

INNER JOIN sys.database_principals members

    ON rolemem.member_principal_id = members.principal_id

where 

 roles.name like  'db_owner'

To find out who is sysadmin on the SQL database:

SELECT createdate,accdate, name FROM sys.syslogins WHERE sysadmin = 1

 

 

More Information:

https://stackoverflow.com/questions/2445444/how-to-get-a-list-of-users-for-all-instances-databases

https://dev.to/jsgurugit/list-users-in-sql-server-database-1k20

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-role-members-transact-sql?view=sql-server-ver15

https://www.sqlserver-dba.com/2015/05/find-database-users-mapped-to-db_owner-role.html

List of Users with db_owner Role in All Databases

https://www.mssqltips.com/sqlservertip/6828/sql-server-login-user-permissions-fn-my-permissions/