List logins on SQL Server instance

Query below returns list of logins in current SQL Server instance.

Users vs logins

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

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


select as login,
       sp.type_desc as login_type,
       case when sp.is_disabled = 1 then 'Disabled'
            else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
          on sp.principal_id = sl.principal_id
where sp.type not in ('G', 'R')
order by;


  • login - user name
  • login_type - principal type:
    • SQL_LOGIN - SQL login
    • WINDOWS_LOGIN - Windows login
    • CERTIFICATE_MAPPED_LOGIN - Login mapped to a certificate
    • ASYMMETRIC_KEY_MAPPED_LOGIN - Login mapped to an asymmetric key
  • password_hash - for SQL logins hashed password with SHA-512
  • create_date - date the login was added
  • modify_date - date the login was last updated
  • status - status of the login
    • Enabled
    • Disabled


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

Sample results

sample results


You can vew logins using SQL Server Management studio. Expand Server -> Security -> Logins branch in Object Explorer.


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.