Query below returns list of users in current database.

Users vs logins

• Login grants access to the server

• User grants a login access to the database

  One login can be associated with many users but only in different databases

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
order by username;

Columns

• username - user name
• create_date - date the account was added
• modify_date - date the account was last updated
• type_desc - principal type:
  • CERTIFICATE_MAPPED_USER - User mapped to a certificate
  • EXTERNAL_USER - External user from Azure Active Directory
  • ASYMMETRIC_KEY_MAPPED_USER - User mapped to an asymmetric key
  • SQL_USER - SQL user
  • WINDOWS_USER - Windows user
• authentication_type - type of user authentication
  • NONE : No authentication
  • INSTANCE : Instance authentication
  • DATABASE : Database authentication
  • WINDOWS : Windows Authentication

Rows

• One row represents one user in the database
• Scope of rows: all users in the database
• Ordered by user name

Sample results

Those results show that there are 5 users in current database.

SSMS

You can vew database users using SQL Server Management studio. Expand Database -> Security -> Users branch in Object Explorer. This option shows also roles and Windows groups.