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.


There are no comments. Click here to write the first comment.