INFORMATICS

The Best

Users and logins in MSSQL

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

Users and logins in MSSQL

Login - grants access to the server

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

Query

select sp.name as login,
       sp.type_desc as login_type,
       sl.password_hash,
       sp.create_date,
       sp.modify_date,
       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 sp.name;

Columns

  • 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

Rows

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

 

 

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
      and name != 'guest'
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

Search