List users in SQL Server database

Query below returns list of users in current database.

Users vs logins

  • Login grants access to the server - List logins in SQL Server
  • User grants a login access to the database

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


select name as username,
       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;


  • 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


  • 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 3 users in current database.

sample results


You can vew database users using SQL Server Management studio. This option shows also roles and Windows groups.


Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.