Displays information about a login account. By using a wildcard expression (%), you can also obtain information about matching logins. Also displays the encryption versions of the login password stored on disk.


sp_displaylogin ['user_id' | '[loginame | wildcard]'




There are additional considerations when using sp_displaylogin:
  • The sp_passwordpolicy security options are taken into consideration when displaying login information related to password expiration, maximum failed logins, and password length.

  • sp_displaylogin displays the encryption version(s) used for a login. For example, when both old and new encryption is used during the password downgrade period, the output of sp_displaylogin has the new line “Password encryption.”

  • sp_displaylogin displays configured roles, so even if you have made a role inactive with the set command, it is displayed.

  • Login triggers associated with the login in question are specified through a login profile. For more information, see Managing Login Accounts and Login Profiles in the System Administration Guide.

  • When you use sp_displaylogin to get information about your own account, you do not need to use the loginame parameter. sp_displaylogin displays your server user ID, login name, login profile, full name, any roles that have been granted to you, date of last password change, and whether your account is locked.

  • If you are a system security officer or system administrator, you can use the loginame parameter to access information about any account.


The permission checks for sp_displaylogin differ based on your granular permissions settings.


With granular permissions enabled, you must be a user with manage any login privilege or manage sever privilege.

Any user can execute sp_displaylogin to display information about their own login account.


With granular permissions disabled, you must be a user with sa_role or sso_role.

Any user can execute sp_displaylogin to display information about their own login account.


Values in event and extrainfo columns from the sysaudits table are:



Audit option


Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference